Avatar of James Edwards
James Edwards
Flag for United States of America asked on

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.
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Nakul Vachhrajani

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.
Scott Pletcher

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).
James Edwards

ASKER
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
James Edwards

ASKER
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?
Scott Pletcher

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.
James Edwards

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

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.
James Edwards

ASKER
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;
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
James Edwards

ASKER
Thank you for your assistance!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Pletcher

You're welcome!  Good luck with the migration ... they can be easy or they can be a royal pain.