January 10, 2009 at 7:05 pm
I found some underlying hardware problems that are going to require us to tear down our array.
We decided to take this opportunity to upgrade our server hardware and reevaluate our DB DR plan.
I need to replicate all the databases, logins, jobs, etc over to the new box. Did I mention also I have a
a user db that is taking some errors, and will have to be repaired before the move. I can not get a good back up on this db right now.
I have configured the array and loaded the os, sql server 2005, patches, etc on the new server.
I also created the volumes with the same drive letters ,etc.
So the question is what is the best approach to migrate my databases over to the new box.
- Detach / Reattach
- Full Backup and Restore
- Copy Database using the wizard in Server 2005
This is my first time moving a SQL 2005 database between servers, so I am looking for feedback on someone who is familiar with the pitfalls.
Thanks
D
January 11, 2009 at 9:50 am
I never used the copy database wizard, so I can’t comment about it. Detach/attach is faster then backup and restore, but it causes down time on the original server. Backup and Restore is slower, but it doesn’t cause downtime. By the way, there are other things that need to be checked when you move databases. SQL Logins should be mapped again to database’s users (check sp_change_users_login in BOL), jobs need to defined again and also SSIS packages.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 11, 2009 at 10:05 am
i would detach then re attach. Backup and restore remember that if you backup the database and restore, if any transactions get made against the old database then you'll need to re run the op again. Use the script at this link from MS to export logins
http://support.microsoft.com/kb/246133
or just create new logins and use the SP mentioned by Adi
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 11, 2009 at 10:19 am
I'm not sure one is much faster than the other, but you need to determine why you can't get a backup. What is the error?
If you detach/attach, be sure you don't "move" the files. Copy them so that you have a backup that way if you need it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 11, 2009 at 10:32 am
unless your using Redgate or Litespeed, you have have to backup the database which can take a while depending on the size. You then have to copy that file across the network and restore it
detach without updating stats is way quicker then just the file copy across the network. I have found it generally quicker to detach then re attach
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 11, 2009 at 10:39 am
If you have a lot of logins defined use the sp_help_revlogin to transfer the logins, by far the best method as you don't have to resynch them afterwards, and it maintains the default database. You will have to edit the script to remove the accounts created by the initial install. If you only have a small number recreate them. Don't forget to check default language and any server roles.
Script out the jobs via SSMS. If you only have jobs created as part of a maintenance plan those are best recreated from scratch by recreating maintenance plans on the new server
If you have local SSIS packages use the save as feature to save them to the new server, or unload them using DTUTIL. Depending on what they do and how they are coded you may have to amend the connection string for the new server name.
Placed any user procs in master or msdb? you'll need those as well.
Don't use the copy database wizard. It's a logical copy so is slow and is the least reliable method. I would use backup\restore but that's just a personal preference, backup\restore and detach\attach are both good, which is best depends on size and number of databases, the larger in size and number for the databases the more detach\attach becomes favorite. If you detach\attach COPY the files not move in case of network glitches and so you have an easier backout (yes you better have a backout plan)
One last thing, you say you have a database with errors? Is it corrupted and marked suspect? in which case DO NOT detach it, you probably will not be able to re-attach it. Makes sure it is clean first.
---------------------------------------------------------------------
January 11, 2009 at 10:47 am
True, you have to do the backup, but you don't have to stop work while you do it and can keep the DB up.
If that doesn't matter, detach will be quicker.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 11, 2009 at 10:51 am
The backout process is also easier with the backup\restore method. You can also make the DB read only before you back up.
---------------------------------------------------------------------
January 11, 2009 at 11:58 am
Thanks for all the feedback. Basically this is a small setup only 3 databases, and 7 logins. The largest DB is 21 GB, so It is looking like a restore maybe the best option.
I have one DB that has a file in the group taking errors. I already know I am going to have to do a repair with data loss to get the DB to a state I can backup. Once I get both instances up, how much effort is there to sync the databases? Right now I plan to take the old sql server offline during our maintenance window, get the backup, and handle the migration. Plan B would be to bring both servers up and try to sync, and then cut over the web app.
January 11, 2009 at 12:23 pm
you don't have a clean backup of your corrupted database you can restore? Usually first resort when fixing a corruption.
If you want to 'synch' the databases with both instances up this would mean doing the initial restore of the full backup in norecovery mode, then taking a transaction log backup to capture all changes since and restoring that with recovery. So how long that takes will depend on size of tran log backup and how long it takes to copy. Will be quicker than full backup\restore.
Use this method if minimizing downtime is vital to you.
---------------------------------------------------------------------
January 12, 2009 at 9:11 am
If all of your paths are staying the same and you can handle some downtime then to me the easiest is to install sql on the new server at the same patch level as your current server. Stop services on both boxes and copy all mdf and ldf files (including the system databases) to the new server then start the services. If the locations are different then detach the databases prior to shutting down the services on the old server. Move all the files and then reattach the databases. This way you don't have to worry about anything, it will all be there just as it was on the old server.
January 12, 2009 at 11:02 am
jncarter (1/12/2009)
If all of your paths are staying the same and you can handle some downtime then to me the easiest is to install sql on the new server at the same patch level as your current server. Stop services on both boxes and copy all mdf and ldf files (including the system databases) to the new server then start the services. If the locations are different then detach the databases prior to shutting down the services on the old server. Move all the files and then reattach the databases. This way you don't have to worry about anything, it will all be there just as it was on the old server.
I would never copy and paste the system database files, that gets you into having to make amendments to the master database and you are mucking about with the resource database, its just higher risk than the logical copy for the info in the system databases.
In SQL 2000 a straight copy of the msdb used to work really well, but now that sysjobs.originating_server has changed in use and SSIS has been 'removed' from the core of SQL, its just as complex as a logical copy and less guaranteed to work, which is a damned shame.
---------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply