DB Restore in SQL Server 2008
Restores with T-SQL The command to restore a DB in SQL Server is aptly named RESTORE. The RESTORE command is similar to the BACKUP command in that it can be used to restore a DB, part of a DB, or a transaction log. You restore an entire DB or part of a DB by using the RESTORE DB syntax. You do transaction log restores by using the RESTORE TRANSACTION syntax.
--To Restore an Entire DB from a Ful l DB backup (a Complete Restore):
RESTORE DB { DB_name \ &DB_name_var }
[ FROM <backup_device> [ ,... n ] ]
[ WITH
[ { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | ST0P_0N_ERR0R } ]
[ [ , ] ENABLE_BROKER ]
[ [ , ] ERROR_BROKER_CONVERSATIONS ]
[ [ , ] FILE = { file_number \ @file_number } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] MEDIANAME = { media_name \ &media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword \
&mediapassword_variable } ]
[ [ , ] MOV E 1
logical_file_name' TO 1
operating_system_file_name' ]
[ ,... n ]
[ [ , ] NEW_BROKER ]
[ [ , ] PARTIAL ]
[ [ , ] PASSWOR D = { password \ &password_variable } ]
[ [ , ] { RECOVERY | NORECOVERY | STANDBY =
{standby_file_name \ &standby_file_name_var }
} 1
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] RESTRICTEDJJSER ]
[ [ , ] { REWIND | NOREWIND } ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] { STOPAT = { date_time \ @date_time_var }
| STOPATMARK = { 'mark_name' | 'lsn\lsn_number' }
[ AFTER datetime ]
| STOPBEFOREMARK = { 'mark_name' | 1
lsn\lsn_number' }
[ AFTER datetime ]
} 1
[ [ , ] { UNLOAD | NOUNLOAD } ]
Once again, there are many available options for restoring a DB, but a simple restore is fairly straightforward. The subsequent example demonstrates a full restore of the AdventureWorks2008 DB:
RESTORE DB [AdventureWorks2008]
FROM DISK = N1
D:\mssql2008\backup\AdventureWorks2008_FullRecovery.bak1
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10