-- Restoring last fill backup

RESTORE DATABASE recovery_test

FROM DISK = 'D:\backups\recovery_test_db_200509150200.BAK'

WITH NORECOVERY,

MOVE 'recovery_test_Data' TO 'D:\data\recovery_test_data.mdf', 

MOVE 'recovery_test_dev_Log' TO 'D:\data\recovery_test_log.ldf'

-- Applying transaction log

RESTORE LOG recovery_test

FROM DISK = 'D:\recovery_test_tlog_200509150300.TRN'

WITH NORECOVERY

-- Applying LAST transaction log

RESTORE LOG recovery_test

FROM DISK = 'D:\recovery_test_tlog_200509150400.TRN'

WITH RECOVERY

NOTES:
1) all file locations need to be visible by SQL server. That means that you need to copy backups to server local drive/raid array; mounted network drives are not accessible (by default). You get the list of drives that sql can see when you open enterprise manager, do 'attach database' and click the browse button on top.

2) file names inside the bak file are not always same then database name (happens when original database that got created first place wasn't w/the same name). If you not sure, run this:

RESTORE FILELISTONLY FROM DISK = 'D:\backups\recovery_test_db_200508300100.BAK'

file names are in the first returned column (LogicalName).

3) ALL files used need to have option 'WITH NORECOVERY', except last one, which puts db into operational state. In case you forget to specify 'WITH RECOVERY' with your last file, you can fix it with following command:

RESTORE DATABASE recovery_test WITH RECOVERY

4) all applied transaction log backups need to be ordered by creation time.


P.S. I strongly suggest automating the process of creating that script: when production system is down, writing that file manually during such stressed out moments is taking lots of time and since every detail is extremly important, you can't afford mistakes. Watch out that RECOVERY option - it can only executed once, after that it's not possible to apply additional transaction logs and you need to start all over. By default SQL server does it WITH RECOVERY.