0

I need to migrate approximately 10 SQL Server databases totalling 400 GB from one VM to another. Some databases are in simple recovery mode, while others are in full. Below is my general plan:

  1. A day before the migration take a full backup of all databases on old VM and restore them on new VM in NORECOVERY mode

  2. On day of migration I will do this in order:

  • ask application team to turn applications off
  • bring dbs to single user mode or restricted mode in old VM
  • perform checkpoint command on all user databases in old VM
  • take cumulative backup of all user databases in old VM
  • once cumulative backups are done, bring dbs in old VM to offline mode
  • restore cumulative backups in new VM WITH RECOVERY
  • Ask application team to turn apps on, pointing to new VM

My question is, in this workflow, is there any chance I might miss some transactions in the transaction log files? Since some databases are in simple recovery mode and others in full, im trying to avoid getting the transaction logs involved in the migration process at all.

Im hoping that by asking the application team to turn the apps off + bringing dbs to single user mode or restricted mode + doing checkpoint operations there is a zero % chance of missing any transaction despite using cumulative backups instead of log backups in the migration process.

Thanks for your input ahead of time

Improve this question
10
  • 1
    Why are you performing manual checkpoints? Backups create an internal checkpoint to ensure data consistency.
    – Dan Guzman
    Commented Dec 3 at 14:11
  • 2
    It's probably not 0, but very close to it. But the important thing is that you have a point in time in which the business says they are ok not having data. As long as you do the steps you say AFTER that point then what you get is what you get.
    – Jonathan Fite
    Commented Dec 3 at 14:11
  • 1
    When you say "cumulative" do you mean DIFFERENTIAL backups? Why would you do this rather than a tail-log backup, which automatically sets the database offline? See eg How to restore transactions after full backup which might be a duplicate of this
    – Charlieface
    Commented Dec 3 at 15:00
  • 2
    Instead of single user mode or restricted mode I'd switch databases to read_only with rollback immediate. But don't forget to turn it back to read_write after restore
    – SergeyA
    Commented Dec 3 at 15:04
  • 1
    @J.D. thanks, this is probably what I will do
    – yzman
    Commented Dec 4 at 13:15

1 Answer 1

Reset to default
1

I's say you've taken the right steps. If you can reach a point in time where no legitimate user or application can modify the databases, then you can safely take the "final backup", and use that for the final restore. Differential or transactional, doesn't really matter, unless downtime is a critical factor--depending on activity, a final transaction log backup/restore could be significantly smaller and faster than differential.

When I've done moves like this before, I've made a checklist (useful when asking around) of everything that "legitimately" accesses the database, and figuring out how to disable it. This can include:

  • Disable (not drop!) all server-level application and user logins that access the database(s)
  • Review SQL Agent jobs. Disable as necessary, and make sure they're not still running.
  • Are linked servers an issue? Figure out how to block them as well.
  • Anything else? Identify and take care of them as well.

When nothing can legitimately modify the database, then it's as safe as it's going to be to take the final backup and go from there.

Improve this answer

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.