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

Posted by digiguru on Server Fault See other posts from Server Fault or by digiguru
Published on 2010-05-26T08:32:33Z Indexed on 2012/06/24 9:17 UTC
Read the original article Hit count: 237

Filed under:
|
|
|

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?

© Server Fault or respective owner

Related posts about sql-server

Related posts about backup