How to move a database using a differential backup?

Question

Hi,

I'm using SQL Server 2005.

I need to move a database to a new server with minimum downtime.

Currently my option is to take everything offline, perform a backup, copy the backup to the new server, restore the backup and bring everything back online.

The problem here is that the backup file is about 10Gb, therefore, it takes about 5-10 mins to perform this whole operation.

I've heard of people performing a full backup with everything online and then doing a differential backup so there is less to copy over.

I've taken the full backup, copied that to the new server and restored it. I've then taken a differential backup and copied it to the new server.

The problem is, when I try and restore this backup I get the message "The log or differential backup cannot be restored because no files are ready to rollforward."

I have also tried selecting both the full and differential backup but then I get the error "The volume on device 'D:\FullBackup.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set."

Does anyone know what I'm doing wrong and what the easiest way of doing this is?

Thanks

Answer 1

It seems you did not set the NORECOVERY option on the full RESTORE.

You need this option to leave all transactions intact so that you can apply the differential backup later.

If you don't set it, the uncommited transactions get rolled back while restoring the full backup, and the last LSN of the database becomes greater than the first LSN of the differential backup.

Answer 2

Another quick way of doing this is to Detach the database, and then copy the underlying .mdf and .ldf files for your DB straight to the DB, then reattach. Time taken to copy a 10GB file will be much less than the Backup, and it acheives the same thing.