Your question, your audience. Choose who sees your identity—and your question—with question security.
SQL2005 to SQL2008R2 side by side migration
ServerA (source server) is running Windows server 2008 with SQL server 2005 SP4 which is hosting a 90GB database. ServerB (destination server) is fresh, scratch built VM running Windows server 2012 R2 with SQL server 2008 R2. I've researched various ways to migrate the database (backup/restore, log shipping, etc). Which is the easiest method to migrate this 90GB database? I have a 2 hour maintenance window to get this done. This is my first time being tasked with migrating a SQL database, so I do not know what to expect nor do I know of any gotchas that may occur during the process.Any tips/advice would be greatly appreciated.
Who is Participating?
Depending upon time data speeds between the two servers, my vote would be to go for a backup/restore. If both are on premise, you could probably move the entire backup once the older database has been made offline.
If data speeds are not that great, I would restore the last known full backup (e.g. the one taken the previous night) using the WITH RECOVERY option. Before taking the older database offline, take a tail log backup. On the new server, you would just need to restore the most recent differential and log backups.
Once the restore is done, you would of course need to do post tasks like setting up security (logins/users), agent jobs, maintenance jobs, changing of the compatibility level, updating statistics, rebuild indexes, etc.
If data speeds are not that great, I would restore the last known full backup (e.g. the one taken the previous night) using the WITH RECOVERY option. Before taking the older database offline, take a tail log backup. On the new server, you would just need to restore the most recent differential and log backups.
Once the restore is done, you would of course need to do post tasks like setting up security (logins/users), agent jobs, maintenance jobs, changing of the compatibility level, updating statistics, rebuild indexes, etc.
1) Back up the master and msdb dbs; optionally model db.
2) Restore the master db.
3) Restore the msdb db; optionally model db.
4) Disable all the jobs.
That will prep the new environment.
Then, ahead of time but as close to the final cut-over as you can but still leaving plenty of time for any mix-ups or failures:
1) Back up the user db(s).
2) Make sure no more full backups run on the old instance.
3) Restore the user db(s) WITH NORECOVERY.
In order to test, you can do WITH RECOVERY, then, after testing, do the final version WITH NORECOVERY.
At final cut-over time, for each user db:
1) Get in the context of that db:
USE [user_db_name];
2) Put db into single_user (or restricted) mode.
ALTER DATABASE [user_db_name] SET SINGLE_USER /*or RESTRICTED_USER*/;
3) Do a differential backup.
4) Switch to the master db.
USE master;
5) Take the user db offline.
ALTER DATABASE [user_db_name] SET OFFLINE WITH ROLLBACK IMMEDIATE;
6) Copy the diff backup to the new instance.
7) Restore the diff backup WITH RECOVERY.
When ready, re-enable all the jobs (of course you may want to leave the job disabled if it was disabled on the original instance).
2) Restore the master db.
3) Restore the msdb db; optionally model db.
4) Disable all the jobs.
That will prep the new environment.
Then, ahead of time but as close to the final cut-over as you can but still leaving plenty of time for any mix-ups or failures:
1) Back up the user db(s).
2) Make sure no more full backups run on the old instance.
3) Restore the user db(s) WITH NORECOVERY.
In order to test, you can do WITH RECOVERY, then, after testing, do the final version WITH NORECOVERY.
At final cut-over time, for each user db:
1) Get in the context of that db:
USE [user_db_name];
2) Put db into single_user (or restricted) mode.
ALTER DATABASE [user_db_name] SET SINGLE_USER /*or RESTRICTED_USER*/;
3) Do a differential backup.
4) Switch to the master db.
USE master;
5) Take the user db offline.
ALTER DATABASE [user_db_name] SET OFFLINE WITH ROLLBACK IMMEDIATE;
6) Copy the diff backup to the new instance.
7) Restore the diff backup WITH RECOVERY.
When ready, re-enable all the jobs (of course you may want to leave the job disabled if it was disabled on the original instance).
Hi Nakul, yes, both servers are on premise and are running on a gigabit network. Is in intrusive to backup the database during production hours or is this something that needs to be done during our maintenance window?
Hi Scott, is it necessary to backup the master and msdb dbs model db? The name of the database i am looking to migrate is named: "moveitdmz". I was thinking to do a simple backup of the moveitdmz database to the local server (ServerA), then copy the backed-up database to ServerB, then restore the database to SerevrB. once the restore is complete, I found a Script that would transfer logins and passwords between ServerA and ServerB. Is this accomplishing anything different than what you are proposing?
It depends. There's potentially more in the master db than just logins, in particular the instance settings (max memory, etc.). For a simple, single-db move, you could probably just duplicate the master db settings and re-create the logins, being sure to use the same sid for native SQL logins/users.
Scott, will your method of moving the master and msdb dbs and model db also move any jobs from the old server? Also, can backup these databases (including the "moveitdmz" database during production hours? Will that impact end users' access to that server while it is busy backing up?
Moving msdb will move the jobs, since they are stored in msdb.
Backups don't affect access to the db: the db can still be fully used while a backup is in progress.
FYI, linked server info is also stored in the master db. If you don't restore master, and you do have linked servers, you'll need to recreate all your linked server definitions. You probably don't have endpoints, but those would be in the master db as well.
Backups don't affect access to the db: the db can still be fully used while a backup is in progress.
FYI, linked server info is also stored in the master db. If you don't restore master, and you do have linked servers, you'll need to recreate all your linked server definitions. You probably don't have endpoints, but those would be in the master db as well.
Ok, I think I will follow your steps above. The only parts I am not sure i follow are below and if I can sort that out, I think I got it:
At final cut-over time, for each user db:
1) Get in the context of that db:
USE [user_db_name];
2) Put db into single_user (or restricted) mode.
ALTER DATABASE [user_db_name] SET SINGLE_USER /*or RESTRICTED_USER*/;
3) Do a differential backup.
4) Switch to the master db.
USE master;
5) Take the user db offline.
ALTER DATABASE [user_db_name] SET OFFLINE WITH ROLLBACK IMMEDIATE;
At final cut-over time, for each user db:
1) Get in the context of that db:
USE [user_db_name];
2) Put db into single_user (or restricted) mode.
ALTER DATABASE [user_db_name] SET SINGLE_USER /*or RESTRICTED_USER*/;
3) Do a differential backup.
4) Switch to the master db.
USE master;
5) Take the user db offline.
ALTER DATABASE [user_db_name] SET OFFLINE WITH ROLLBACK IMMEDIATE;
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
To do that, we need to take a differential backup. But, for those who are hyper about missed changes, we also want to make sure that changes don't occur after we take our final backup.
One way to make sure no more changes occur is for us to:
1) Get in the context of that db:
USE [user_db_name];
So now we're using that db.
Then we "tell" SQL that only ONE person can use that db. Since your script is using it, you will then be the ONLY user allowed to use that db. That means no one else can possibly make any changes to the db while it's still in SINGLE_USER mode.
ALTER DATABASE [user_db_name] SET SINGLE_USER --WITH ROLLBACK IMMEDIATE; --if this statement hangs, try it again with the ROLLBACK uncommented
At this point SQL will insure that you are the ONLY task using the db. Now do your final backup:
BACKUP DATABASE [user_db_name] TO DISK = '...' WITH DIFFERENTIAL, ...
So now we have a good backup of the EXACT way that db looks. To prevent anyone from connecting to it -- and thus accidentally using the old version of the db instead of the new one on the new server -- we take the db offline. The advantage of this is that the db is still physically there and we can instantly bring it back online if you need to access it again for any reason (for example, the upgrade failed, so you need to go back to the old version of the db for a while).
You can't a db offline while you are using that db -- since you then wouldn't have a db context at all -- so first we switch to the master db, then we IMMEDIATELY take the db offline.
USE master;
GO
ALTER DATABASE [user_db_name] SET OFFLINE WITH ROLLBACK IMMEDIATE;