SQL Server : Restoring Transaction Log Backups with Transact-SQL

Transaction log backups must be applied to a database that is in RECOVERY or STANDBY mode; that is, you cannot restore the transaction log backup while the database is operational. Full and differential database backups must be applied prior to restoring transaction log backups.

For example, the following commands restore a full backup of the pubs database, followed by restoring the transaction log:
view source
print?
/* 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


Now suppose that you had two transaction log backups to restore, one from 2200 hours and another from 2300. You'd restore the full backup and the first log backup with NORECOVERY and the last transaction log backup with recovery, as follows:
/* restore the full backup first with NO RECOVERY */
<p>RESTORE DATABASE pubs  FROM DISK = 'e:\pubs.bak'  WITH NORECOVERY  
/* then restore the first log WITH NORECOVERY */
RESTORE LOG pubs   FROM DISK = 'e:\pubs_2200.trn'  WITH NORECOVERY  
/* finally restore the second log backup WITH RECOVERY */  
</p>
RESTORE LOG pubs   FROM DISK = 'e:\pubs_2300.trn'  WITH RECOVERY


If you have a differential backup, it must be restored prior to restoring any transaction log backups, as shown in the following example:
RESTORE DATABASE pubs  FROM DISK = 'e:\pubs_full.bak'  WITH NORECOVERY  
<p>RESTORE DATABASE pubs  FROM DISK = 'e:\pubs_differential.bak'  WITH NORECOVERY  
</p>
RESTORE LOG pubs   FROM DISK = 'e:\pubs_2300.trn'  WITH RECOVERY


The next example shows 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 = 'Aug 4, 2003 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.


The next example shows 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'  
<p>INSERT authors (  au_id,  au_lname,  au_fname,  phone,  address,  city,  state,  zip,  contract)
</p>
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