0

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?

1
  • can we detach in 2016 and attach in 2019?
    – dba_rs
    Sep 18, 2023 at 20:35

1 Answer 1

0

If you use backup and restore to move the database from Server A to Server B, your process will be

  • Full Backup on A
  • Restore to B

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.

1
  • Thank you for the input
    – dba_rs
    Sep 19, 2023 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.