Restoring Options in SQL Server

Restoring options

RESTORE WITH RECOVERY

           

            Database will come recover completely and will be useful, Not possible to restore additional backups.

RESTORE WITH NORECOVERY

Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path.

RESTORE WITH STANDBY

Leaves the database in a standby state, in which the database is available for limited read-only access. This option is equivalent to specifying WITH STANDBY in a RESTORE statement.

Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone.

Restoring a Backup

Restore a full backup

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

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 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.

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

GO

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

GO

RESTORE HEADERONLY:

The RESTORE HEADERONLY option allows you to see the backup header information for all backups for a particular backup file.

RESTORE HEADERONLY FROM DISK = 'C:\AdventureWorks.BAK'

GO

RESTORE LABELONLY:

The RESTORE LABELONLY option allows you to see the backup media information for the backup file. 

RESTORE LABELONLY FROM DISK = 'C:\AdventureWorks.BAK'

GO

RESTORE FILELISTONLY:-

The RESTORE FILELISTONLY option allows you to see a list of the files that were backed up.

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'

RESTORE VERIFYONLY:-

The RESTORE VERIFYONLY command checks the backup to ensure it is complete and the entire backup is readable (useful for the restore)

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK

GO