2

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?

digiguru
  • 235
  • 2
  • 14

2 Answers2

2

You have to restore the full backup with NORECOVERY option in order to be able to restore the subsequent differential backups. Only the last restore will use the RECOVERY option. If you need to restore a differential backup each night over the previous one, differential restores has to use the NORECOVERY option too. In this case the database B will be always in a non-operational state.

Danilo Brambilla
  • 1,031
  • 2
  • 15
  • 33
0

When performing the differential, go to the options page & check radio button that says 'Backup to backup to a new media set, and erase all existing backup sets'.

Somewhere you are striping a backup. Using the above option will use the INIT and FORMAT keywords on the DIFF backup.

Kenneth
  • 111
  • 2