Moving a 300GB production database from an aging 2012 box to new hardware running SQL Server 2016. Downtime window is 4 hours on a Sunday. Backup-restore or detach-attach, and what are the gotchas?
How to migrate a database from one SQL Server to another?
Solved SQL & Databases
AJ
Andrew Jackson October 17, 2019
2 replies
7,310 views
Reviewed by moderators
Backup-restore, not detach-attach. Detach leaves the source offline the whole copy and one bad sector strands you with nothing mounted.
1
Days before: full backup, restore on the new box WITH NORECOVERY. No downtime yet.
2
Window opens: kick users, take a differential plus tail log backup, restore both WITH RECOVERY on the target. Minutes, not hours.
3
Gotchas: logins live in master so script them across with sp_help_revlogin, fix orphaned users, recreate jobs and linked servers, then update the connection strings.
Compatibility level stays at 2012 after restore. Bump it once the app proves stable.
Cutover took 40 minutes of the 4 hour window. Logins script saved us. Solved.