Home / server / Questions / 145966
Accepted
Rob Sobers
Asked: 2010-05-28 09:54:47 +0800 CST

What does SQL Server do if you select more than 1 full backup when doing a restore?

  • 772

I have a backup file that contains 2 backup sets. Both backup sets are full backups. When I open SQL Server Management Studio and choose "Restore..." and pick the file as my device, it lets me pick both backup sets.

The restore operation completes without error, but I'm not sure exactly what SQL server did. Did it restore the first one, drop the database, and then restore the second one? Will it always let the most recent full backup prevail?

It doesn't seem to make sense for SQL server to even allow you to select more than one full backup.

sql-server backup database restore

1 Answers

  1. Best Answer

    On the Restore database dialog, there is a setting: To a point in time: and the default is Most recent possible. So your restore operation should be restoring only the most recent backup. Here is some more information from MSDN on this.

    Since a full backup includes the Transaction log, it could make sense to include two full backups. The first one could be used for the initial restore and then only the transaction log from the second full backup could be used to update the incremental changes from there.
    Another reason could be if the transaction log was truncated after the first backup was performed and your .MDF file was corrupted in the meantime. At that point you might want the MDF from the first full and only the transaction log from the second full.

    • 1