Restore Database Backups

Overview

There are several RESTORE commands and options that can be used to restore and view the contents of your backup files.

In this next section we will look at the following commands that can be used:

• RESTORE HEADERONLY - gives you a list of all the backups in a file
• RESTORE LABELONLY - gives you the backup media information
• RESTORE FILELISTONLY - gives you a list of all of the files that were backed up for a give backup
• RESTORE DATABASE - allows you to restore a full, differential, file or filegroup backup
• RESTORE LOG - allows you to restore a transaction log backup
• RESTORE VERIFYONLY - verifies that the backup is readable by the RESTORE process

Take the time to get to understand what options are available and what can be done using SQL Server Management Studio and what options are only available via T-SQL commands.

Finally we are going to implement only these two which are RESTORE DATABASE and RESTORE LOG.

RESTORE DATABASE Backup

The RESTORE DATABASE option allows you to restore either a full, differential, file or filegroup backup.

Explanation

When restoring a database will need exclusive access to the database, which means no other user connections can be using the database.

The RESTORE DATABASE option can be done using either T-SQL or using SQL Server Management Studio.

T-SQL

Restore a full backup

This will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup. The original location can be checked by using RESTORE FILELISTONLY.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
GO

Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)

The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO

Restore a differential backup

To restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

Restore using a backup file that has multiple backups

Let's say we use the same backup file, AdventureWorks.BAK, to write our full backup and our differential backup. We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that in position 1 we have a full backup and in position 2 we have a differential backup. The restore commands would be.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY, FILE = 1
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 2
GO

RESTORE LOG Backup:

The RESTORE LOG command allows you to restore a transaction log backup. The options include restoring the entire transaction log or to a certain point in time or to a certain transaction mark. In order to restore a transaction log backup, the database has to be in a restoring state which means that a full backup or a full backup and differential backup were first restored using the NORECOVERY option to allow additional backups, like transaction log backups to be restored.

Explanation

When restoring a transaction log you will need exclusive access to the database, which means no other user connections can be using the database. If the database is in a restoring state this is not an issue, because no one can be using the database.

The RESTORE LOG option can be done using either T-SQL or using SQL Server Management Studio.

T-SQL

Restore a transaction log backup

To restore a transaction log backup the database need to be in a restoring state. This means that you would have to restore a full backup and possibly a differential backup as well.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

Restore multiple transaction log files (NORECOVERY)

The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off. As was mentioned above the database needs to be in a restoring state, so this would have already been done for at least one backup file that was restored.

This shows restoring two transaction log backups, the first using NORECOVERY and the second statement does not which means the database will be accessible after the restore completes.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'
GO

Restore multiple transaction log backups from the same backup file

Let's say we use the same backup file, AdventureWorks.TRN, to write all of our transaction log backups. This is not a best practice, because if the file is corrupt then this could corrupt all of your backups in this file. We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that we have 3 transaction log backups in this file and we want to restore all three. The restore commands would be.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 2
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH FILE = 3
GO