Migrating Databases from old version to new version- in shortest time

Question

Migrating Databases from old version to new version- in shortest time

sqlguy80

SSCrazy

Points: 2763
More actions
March 17, 2017 at 10:36 pm

#341778

Hi,
To migrate lots of databases (~1200 ) , ( some of them VLDB 500 GB ) on a Fri- Sun Maintenance Window
1.Schedule a maintenance plan job to FULL backup all dbs to disk.
2.On Thu night - restore all Databases on destination server WITH NO RECOVERY.
Log backups are running every 30 minutes anyways
3.On Friday night ( say 11 PM )- schedule a FINAL log backup to disk ( Tail Log backup )
4. Use Restore Script which restores Full Backup with no recovery , Tlog Backups with no recovery and Lastly Tail log backup with no recovery and restore database WITH RECOVERY?
Any steps missed which could cause data loss?
On Friday- around 10 PM if I stop SQL Server Service so that no one can connect . and then after 15 min bring it up and then run the tail log backup will it work or break the LSN or cause any data loss?
Thanks in Advance,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

The steps sound fine except that any webservices will latch on to the databases as soon as they can. You might want to set each DB so single user as the precursor to taking the tail-log backup.

Also, rRather than experimenting with ~1200 databases, do a restore of one of the databases into a test database and try your plan on that one database. Work out any bugs and then try a small number of the databases copied to test databases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 2

Create scripts to set each DB to single user mode, take a DIFF DB backup then set the DB to Off-line. Then just restore the DIFF with recovery on the target system and forget restoring all of the TLog backups.

vineet_dubey1975 SSCrazy Points: 2303 More actions · Answer 3

Also do take care of the SystemdB (Master,Model,MSDB) for Users and the jobs if any

Perry Whittle SSC Guru Points: 233853 More actions · Answer 4

sqlguy80 - Friday, March 17, 2017 10:36 PM
Hi,
To migrate lots of databases (~1200 ) , ( some of them VLDB 500 GB ) on a Fri- Sun Maintenance Window
1.Schedule a maintenance plan job to FULL backup all dbs to disk.
2.On Thu night - restore all Databases on destination server WITH NO RECOVERY.
Log backups are running every 30 minutes anyways
3.On Friday night ( say 11 PM )- schedule a FINAL log backup to disk ( Tail Log backup )
4. Use Restore Script which restores Full Backup with no recovery , Tlog Backups with no recovery and Lastly Tail log backup with no recovery and restore database WITH RECOVERY?
Any steps missed which could cause data loss?
On Friday- around 10 PM if I stop SQL Server Service so that no one can connect . and then after 15 min bring it up and then run the tail log backup will it work or break the LSN or cause any data loss?
Thanks in Advance,

To make it clearer should be

1.Schedule a maintenance plan job to FULL backup all dbs to disk.
2.On Thu night - restore all Databases on destination server WITH NO RECOVERY.
Log backups are running every 30 minutes anyways
3.On Friday night ( say 11 PM )- schedule a FINAL log backup to disk ( Tail Log backup ) , use backup log with norecovery, will put source databases in restore mode
4. Use Restore Script which restores Full Backup with no recovery , Tlog Backups with no recovery and Lastly Tail log backup with recovery

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

richardwillemain SSC Veteran Points: 291 More actions · Answer 5

sqlguy80 - Friday, March 17, 2017 10:36 PM
Hi,
To migrate lots of databases (~1200 ) , ( some of them VLDB 500 GB ) on a Fri- Sun Maintenance Window
1.Schedule a maintenance plan job to FULL backup all dbs to disk.
2.On Thu night - restore all Databases on destination server WITH NO RECOVERY.
Log backups are running every 30 minutes anyways
3.On Friday night ( say 11 PM )- schedule a FINAL log backup to disk ( Tail Log backup )
4. Use Restore Script which restores Full Backup with no recovery , Tlog Backups with no recovery and Lastly Tail log backup with no recovery and restore database WITH RECOVERY?
Any steps missed which could cause data loss?
On Friday- around 10 PM if I stop SQL Server Service so that no one can connect . and then after 15 min bring it up and then run the tail log backup will it work or break the LSN or cause any data loss?
Thanks in Advance,

I'd suggest not shutting down sql, but rather switch into single-user mode with some param -- forget the syntax but check on technet, BOL. After you are in single-user do your tail-of-log. I assume then that this is the start of your window and all of these databases will not have any user activity, etc. So you might consider taking them offline, if possible with your overall plan. Best luck.

cheshirefox Hall of Fame Points: 3464 More actions · Answer 6

Have you considered attach? Would be cleaner solution due to the actual maintenance window.
Basic steps:
1. Shut down SQL Services
2.Copy entire directory of database/ transaction log onto new SQL instance.
3.Attach the databases.

Best Part - all the specific SQLs task can be scripted. The only manual process (which can be scripted as well) is the copying of the database files.

dave.hobbs Old Hand Points: 313 More actions · Answer 7

1200 databases sounds like a lot to do manually, so here are a few thoughts:
- how are you planning to deal with any backup or restore failures?
- Are you planning to run all these commands in one big block or migrate each database in turn?
- Have you considered a rollback plan just in case everything doesn't work out?

I assume that all these databases are used by lots of different applications rather than a single app, so it might be better to take each application offline, migrate the relevant databases and bring the app back online, which would avoid having all the applications down for the entire migration weekend. I would consider writing a control process, possibly in powershell, that would run the whole migration. This way you could add in error trapping, logging and possibly retry steps, and deal with any issues in isolation (hopefully) without disrupting the rest of the migration. If you had a function that ran the following steps :

1 - Take application offline (put up holding page / disable database user / some other method suitable to your application)
2 - Disable any SQL jobs or other batch processing for the DB
3 - Run differential backup on source server
4 - Restore differential backup on target server
5 - Reconfigure application to point to target server
6 - Enable SQL jobs on target server
7 - Bring application online
8 - Run full backup on target server and enable TLog backups

Your control script could loop through the databases calling this function, and so each DB would be down for the minimum amount of time. If you got the function to log it's process to a database table you could easily monitor the process of the migration and pick up issues as they occur. You could also make the script multi threaded to increase the throughput, but you would need to be careful with the number of threads as you don't want to overload your IO system with too many simultaneous backups and restores.

Good luck!