Migrating SQL Server databases using differential backups, NOT transaction log backups

Question

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, I'm trying to avoid getting the transaction logs involved in the migration process at all.

I'm 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.

I'd have to set all databases to full recovery mode to take tail-log backups. I don't want that because I'd have to worry about disks filling up with log files getting larger.

Answer 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.

Answer 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

---

If you switch the recovery model to full during your maintenance window (not allowing access to the database) then the database isn't going to change in size. You can take tail-log backups, restore them to the new server, then switch the recovery model back to simple for the databases that were originally in simple, before turning access back on.

Scenario 1: I take a full backup on old VM and restore in new VM a day before migration. Then on the day of migration when I have 1 hour maintenance window I switch old VM's db to full recovery and take tail log backup to restore on new VM.

Scenario 2: Same as above, but I switch to full recovery mode before taking full db (a day before migration). I feel like I will run into issues if I switch to full recovery mode after taking a full backup (scenario 1). Would scenario 1 actually work?

I believe you need to be in full recovery before taking the full backup else your backup chain will be broken when you go to take the tail-log backup (I'm not 100% sure on that).

But, the order of events I see it as would then be switch to full recovery and take the full backup right before you maintenance window starts, to minimize the time for any growth, then take the tail-log backup after the maintenance window starts, to capture any final transactions and automatically set the database offline. Your average DB size is 40 GB so the full backup should be quick. - j-d

---

See also How to restore transactions after full backup

Answer 3

In your process, you're trying to avoid using transaction log backups and relying on full backups and incremental backups instead. I am sharing a few steps you can take to minimize the risk of missing transactions:

Simple Recovery Mode: For databases in simple recovery mode, transaction logs are truncated automatically after each checkpoint, so there's no need to worry about log backups.

Manual Checkpoints: Checkpoints will remove all dirty pages from memory to disk however, active transaction log may still contain committed transactions not included in your last differential or full backup. The checkpoint command helps, but it doesn't capture all uncommitted transactions.

Improvement Suggestion: To be safe, consider taking a small transaction log backup just before you go offline. This will ensure no transactions are missed.

For full recovery mode databases, adding a quick log backup ensures reliable migration and prevents missing transactions. To confirm database integrity post-migration, use tools like DBCC CHECKDB to check both logical and physical integrity.