Restore DB Options 1

The RESTORE ... WITH RECOVERY option puts the database into a useable state, so users can access a restored database.

The RESTORE ... WITH NORECOVERY option puts the database into a "restoring" state, so additional backups can be restored.  When the database is in a "restoring" state no users can access the database or the database contents. The most common example of this would be to restore a "Full" backup and one or more "Transaction Log" backups.

The RESTORE WITH STATS option will give you an idea of where the restore process currently is an the overall process. This information is presented in percentage of completion. The default is to display after every 10% or a percentage value can be specified.

RESTORE DATABASE 'AdventureWorks' FROM DISK = 'C:\AdventureWorks.BAK' WITH STATS = 1

The RESTORE ... WITH REPLACE allows you to write over an existing database when doing a restore without first backing up the tail of the transaction log.  The WITH REPLACE basically tells SQL Server to just throw out any active contents in the transaction log and move forward with the restore.

The RESTORE ... WITH MOVE option allows you to restore your database, but also specify the new location for the database files (mdf and ldf).  If you are restoring an existing database from a backup of that database then this is not required, but if you are restoring a database from a different instance with different file locations then you may need to use this option. The RESTORE ... WITH MOVE option will let you determine what to name the database files and also what location these files will be created in.  Before using this option you need to know the logical names for these files as well as know where SQL Server will restore the files if you do not use the WITH MOVE option.

               If another database already exists that uses the same file names you are trying to restore and the database is online the restore will fail.  But if the database is not online for some reason and the files are not open, the restore will overwrite these files if you do not use the WITH MOVE option, so be careful you do not accidently overwrite good database files.

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

WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdvnetureWorks_Data.mdf',

MOVE 'AdventureWorks_Log' TO 'H:\SQLData\AdvnetureWorks_Log.ldf'

The RESTORE ... WITH STOPAT option allows you to restore your database to a point in time.  This gives you the ability to restore a database prior to an event that occurred that was detrimental to your database.  In order for this option to work, the database needs to be either in the FULL or Bulk-Logged recovery model and you need to be doing transaction log backups.

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

WITH NORECOVERY

GO

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

WITH RECOVERY, STOPAT = 'Mar 23, 2009 05:31:00 PM'

SELECT database_name, backup_start_date, type, *

FROM msdb.dbo.backupset

WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE ()) AND GETDATE ()

AND Type = 'D'

ORDER BY backup_set_id DESC

Type D – Full Database Backup

L – Transaction Log

I – Differential Backup

F – File or File Group Backup