Difficulty restoring a differential backup in SQL Server, 2 media families are expected or no files are ready for rollforward
- by digiguru
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.
Copy full backup from server A to copy to server B (10+gb)
Open SQL Server Managment Studio on server B
Right mouse on databases
Restore Database
Type in the new DB-name
Choose "From Device" and browse to the backup file
Click Okay. This is now resorting the original "full" backup.
Test new db with dev application - everything works :)
On original database rightmouse on DB Tasks Backup...
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)
Copy the diff backup onto the new db
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?
