Sql-server – SQL Server Database Migration – Backup/Restore while Original DB still in Production

backupmigrationrestoresql serversql-server-2008-r2

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 | 
+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-

Best Answer

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.

Related Question