2

I have a very large DB in full recovery mode. My SQL Server is 2008 R2. The thing is that I need to migrate to a new server while having the original DB still in production.

Both backup and restore will take several hours, so...

  • What happens to the records added into the database while doing the backup? If I'm right, this is not a problem and the .bak file will include all data until the time of the end of the backup. Do I mark the Copy-only?

  • Once the backup is done, and I start to restore... what's the best option to get the rest of the records which have been added to the original DB while restoring in the new server?

I've tried full+differential, but I'm getting a message

the differential backup cannot be restored because the database has not been restored to correct earlier state

in my test environment.

It's sure that it's the last full backup because I'm doing it one minute later after just modifying one record.

Also, I restore the full with norecovery and the differential with recovery.

What do I do wrong?

The backup will be to disk because I need to do it as fast as possible.

Edited:

I'm still getting the message 'the differential backup cannot be restored because...'. What's the problem?

Do i have to do something special while doing the backup to disk? These are the LSNs:

+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-
| BackupType |  |     FirstLSN      |  |          LastLSN  |  | CheckpointLSN     |  | DatabaseBackupLSN | 
+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-
| FullBackup |  | 99000000025100209 |  | 99000000034000001 |  | 99000000033800001 |  | 98000002545900051 | 
| DiffBackup |  | 99000000033800001 |  | 99000000036800001 |  | 99000000035000041 |  | 98000002545900051 | 
+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-
6

First step is to change the recovery mode to FULL.

Take a full backup (with compression), and restore the full backup to new server with NORECOVERY. This will allow you to restore additional log backups.

Now configure logshipping from the primary (old) to secondary (new) server.

During the time of cutover, take a tail log backup and bring the secondary server online by restoring the log backup WITH RECOVERY.

make sure you have instant file initialization enabled on both servers to cut down the restore times.

Refer to my answer for Moving Databases to New Datacenters for more detailed steps.

  • Thanks for your answer. I'm sure your approach is the best, but why does not work when i try the differential? – Horaceman Dec 29 '15 at 17:12
  • When you restore the FULL backup, are you using WITH NORECOVERY? – Kris Gruttemeyer Dec 29 '15 at 17:29
  • 1
    You mentioned using "copy-only" in your backup. Did you check that box? A copy only backup doesn't alter your LSN chain, so if you took a copy-only full and then tried a "copy-only" differential, the differential still wants the last original full backup that wasn't marked as a copy. – 8bit Dec 29 '15 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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