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?