Migrating Differential Backup Restoration in SQL Server

backupmigrationsql server

Good afternoon,

A little back story about what I'm trying to do: I'm migrating a database environment from one SQL server to another. A simple full backup/restore works fine, but there's a possibility that the old environment will still have to be active while I move data to the new environment. Mirroring and log shipping are not options, but there is nothing that is needed to be updated in the database itself for it to work, so I thought I'd run a differential on one and restore it to the migrated database location.

I tried to do this already and found out that I'm already out of the recovery phase after my first full database restoration. I guess my real question is can I do a full backup on my current location database, and a differential on my previous location database and have it work the same as if I restored a full and dif backup from the same location database?

Also, if there are log backups between the times I did a diff backup of the original database, do I need to include them as well?

Please let me know if you need clarification.

Best Answer

Welcome to the forum. You do not need to do log restores between a FULL and a DIFF backup in SQL Server. If you do you'll break the LSN (Log Sequence Number) used to mark the delta's between the restores. A differential backup is all the transactions from the last full backup. SQL Server notes the last full backup in a data page when it backs up so it knows what data pages have changed. At that point any DIFF backup from the last FULL backup will work.

Note the newer versions of SQL Server have a 'COPY ONLY' option which will NOT affect your LSN or modified pages since last FULL backup. So basically don't use COPY ONLY.

Finally you'll want to do a restore with NORECOVERY. I know this sounds counter intuitive at first but basically you're telling the engine do not play back the log files.

--This restores the full DB but does not replay the open transactions at the time, leaving it consistent for a DIFF or transaction log restore
RESTORE DATABASE DATABASE FROM '<pathofDBBackup>' WITH NORECOVERY;

--This is your DIFF restore and at the end you can recover.
RESTORE DATABASE DATABASE FROM '<pathofDIFFDBBackup>' WITH RECOVERY;

In case you needed more info:

Understanding How Restore and Recovery of Backups Work in SQL Server

The excellent backup internals videos by Paul Randall.

Backup Internals Demo

Related Question