Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
What is the best (i.e. fastest) way (backup and restore) for migrating a 6 TB database in SQL Server 2016 (Windows 2012r2) to another server, SQL Server 2019 (Windows 2019)?
Can we, for example, detach the database on SQL Server 2016 and attach on SQL Server 2019?
If you use backup and restore to move the database from Server A to Server B, your process will be
Given this is a 6TB database, both of these processes will inevitably take time, lets say for the sake of argument it takes two hours to back up and two hours to restore.
If your backup starts at 12PM, all the changes made from 12PM will be included in the backup file, the backup completing at 2PM. If you then start your restore on Server B at 2PM, that means at 4PM when the restore completes, the database on Server B will be two hours behind as changes have been made to Server A in the time taken to restore to Server B and these are not in the backup.
From here you would then need to capture the changes that took place on Server A during the period your backup was restoring on Server B, your options would be a differential backup and / or a log backup.
You could do the following to ensure that no further changes are made to the database on Server A once the final log backup takes place:
BACKUP LOG YourDB TO DISK = '\\somewhere\backup.trn' WITH NORECOVERY
at this point, your database on Server A goes into NORECOVERY and is inaccessible, you now have an outage which lasts the time it takes to restore the log to server B (WITH RECOVERY) and also re-point all your apps etc
This process can be made a little smoother by setting up log shipping, mirroring or availability groups ahead of the migration and "flipping the switch" when you are ready to failover, this should give minimal downtime.
