Different types of Database Restore option

RESTORE FILELISTONLY retrieves the list of database and log files in the backup.

RESTORE HEADERONLY returns header information about all backup sets on the supplied backup device. Although this command returns a wealth of information,

typically you will only examine a small portion of it. For instance, it might be beneficial to know a backed up database’s collation, type of backup, first and last Log

Sequence Numbers and database compatibility level.

RESTORE FILELISTONLY

FROM DISK ='e:\telefullbkp'

RESTORE HEADERONLY

FROM DISK ='e:\telefullbkp'

RESTORE FILELISTONLY

FROM DISK = 'D:\backup\Banking_20120929192001.trn'

RESTORE HEADERONLY

FROM DISK = 'D:\backup\Banking_20120929192001.trn'

*********************************************************************************************

/* restore the full backup first with NO RECOVERY */

RESTORE DATABASE pubs

FROM DISK = 'e:\pubs.bak'

WITH NORECOVERY

/* then restore the log WITH RECOVERY */

RESTORE LOG pubs

FROM DISK = 'e:\pubs_2300.trn'

WITH RECOVERY

*****************************************************************************

-- How to restore transaction logs up to a point in time.

RESTORE DATABASE pubs

FROM DISK = 'e:\pubs.bak'

WITH NORECOVERY

RESTORE LOG pubs

FROM DISK = 'e:\pubs_2300.trn'

WITH STOPAT = 'Mar 4, 2013 11:55:55AM', RECOVERY

If you specify a point in time in the future (that is, a point in time that is not recorded in the transaction log backup) you will get the following message:

This log file contains records logged before the designated point-in-time. The database is

being left in load state so you can apply another log file.

*****************************************************************************

--How to restore a transaction log up to a marked transaction. First, let’s suppose that we’ve taken a full backup of pubs database as follows:

BACKUP DATABASE pubs

TO DISK = 'e:\pubs.bak'

WITH INIT

--Next we run a marked transaction, adding a record to the authors table as follows:

BEGIN TRANSACTION TransactionName WITH MARK 'TransactionMark'

INSERT authors (

au_id,

au_lname,

au_fname,

phone,

address,

city,

state,

zip,

contract)

SELECT '237-44-4502', 'brown', 'annie', '234-32-3334', '223 amd st',

'namecity', 'tn', '33949', 0

COMMIT TRAN

--Further suppose that we ran other transactions which compromised the integrity of the pubs database. Now we wish to restore pubs up to the transaction called

--'TransactionName'. To do so, first restore the full backup and then apply the transaction log backup with the STOPATMARK keyword:

RESTORE DATABASE pubs FROM DISK = 'e:\pubs.bak'

WITH NORECOVERY

RESTORE LOG pubs

FROM DISK = 'e:\pubs_log23.trn'

WITH STOPATMARK = 'TransactionName', RECOVERY

--To exclude the transaction called ‘TransactionName’, we would use the same command, except replace STOPATMARK with STOPBEFOREMARK:

RESTORE LOG pubs

FROM DISK = 'e:\pubs_log23.trn'

WITH STOPBEFOREMARK = 'TransactionName', RECOVERY