Difficulty restoring a differential backup in SQL Server, 2 media families are expected or no files are ready for rollforward

Question

I have sql backups copied from server A to server B on a nightly basis.

We want to move the sql server from server A to server B without much downtime, but the files are very large.

I assumed that performing a differential backup and restore would solve the problem with the databases.

1. Copy full backup from server A to copy to server B (10+gb)
2. Open SQL Server Managment Studio on server B
3. Right mouse on databases
4. Restore Database
5. Type in the new DB-name
6. Choose "From Device" and browse to the backup file
7. Click Okay. This is now resorting the original "full" backup.
8. Test new db with dev application - everything works :)
9. On original database rightmouse on DB > Tasks > Backup...
10. Backup Type = Differential, Backup to disk, add a new file, and remove the old one (it needs to be a small file to transfer for the smallest amount of outage)
11. Copy the diff backup onto the new db
12. Right mouse on DB > Tasks > Restore > Database

This is where I get stuck. If I add both the new differential file, and the original backup to the restore process I get an error

The media loaded on "M:\path\to\backup\full.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.
RESTORE HEADERONLY is terminating abnormally.

But if I try to restore using just the differential file I get

System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)

Any idea how to do it? Is there a better way of restoring backups with limited downtime?

Answer 1

See 2008 Books Online Performing a Complete Database Restore for details and code.

Perhaps the error is caused by both full and differential backups trying to be restored at once by the backup device specification (may need to be changed).

Do them one at a time (basic idea):

Create tail_log backup.

Restore full first, with NORECOVERY.

Restore differential, with NORECOVERY.

Restore log, with NORECOVERY.

Restore tail_log, with NORECOVERY.

Then, when all done, Restore with RECOVERY.