SQL Server Performance Forum – Threads Archive

Restore multiple transaction logs

I want to know how to restore database from multiple transaction logs. After restoring from full and differential backups, I was ready to run the following SQL to restore from multiple tran log backups: RESTORE LOG ivr_collections
FROM DISK = ‘z:sql backupuserdbsivr_tranlogbkup.bak’
WITH
MOVE ‘ivr_data’ to ‘H:SQL Serveruserdbsivrivr.mdf’,
MOVE ‘ivr_log’ to ‘H:SQL Serveruserdbsivr.ldf’,
FILE = ????,
NORECOVERY RESTORE LOG ivr_collections
FROM DISK = ‘z:sql backupuserdbsivr_tranlogbkup.bak’
WITH
MOVE ‘ivr_data’ to ‘H:SQL Serveruserdbsivrivr.mdf’,
MOVE ‘ivr_log’ to ‘H:SQL Serveruserdbsivr.ldf’,
FILE = ????,
RECOVERY I was troubled by FILE = ????. My real question is how do I find out correct FILE numbers to put in the above statements? Thanks.

The file numbers are incremented as you append backups to the deice. So, a file number 1 indicates the first backup set and file number 2 indicates second backup set etc. Also, you may get more information from command like RESTORE FILELISTSONLY or RESTORE LABELONLY

When I ran restore headeronly, it returned only one (1) backup set for tran log backup. I think this is because I used ‘Overwrite existing media’ instead of ‘Append to media’ when I backed it up. The following error occurred when I exec the SQL: RESTORE LOG Test
FROM DISK = ‘z:sql backupackupdbs(mars)userdbs est_dbTest_db_tranlogbkup.bak’
WITH MOVE ‘Test_data’ to ‘H:SQL ServerLive Database filesTestTest.mdf’,
MOVE ‘Test_log’ to ‘H:SQL ServerLive Database filesTestTest_log.ldf’,
file=1,
RECOVERY Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 7000000006700001, which is too late to apply to the database. An earlier log backup that includes LSN 7000000006600001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally. I can see multiple backup sets and LSNs by exec the below query: SELECT *
FROM msdb.dbo.backupset AS s
JOIN msdb.dbo.backupmediafamily AS m
ON s.media_set_id = m.media_set_id
WHERE database_name = ‘Test’
ORDER BY 1 ASC But, I don’t know what command to use to restore the backup sets in order. Is it a MicroSoft bug? Or if I use ‘Overwrite existing media’ option, I simply cannot restore multiple tran log backups. Thank you very much in advance for your help.

If you overwrite existing media, I’m afraid you cannot restore multiple tran log.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Some operations, which are either nonlogged, minimally logged or that remove the inactive portion of transaction log, may have an effect on the sequence of the transaction log. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for your tips. I reset backups with ‘Append to media’ and that allowed me to restore database from multiple differential and transaction log backups. The reason I did ‘Overwrite existing media’ was to save disk space. Now with appending to media, my backup sets keep appending and thus consume lots of physical diskspace. Does anyone know how to remove older backup sets (say order than 7 days; e.g., File=1, 2, …7) from a backup (say IVR_DiffBkup.bak)? Your input will be appreciated.

Refer to this linkhttp://www.sqlteam.com/filtertopics.asp?TopicID=115 for BACKup strategy information that may help to assess the issue. Review the article defined by NR in the link.
HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

You could schedule a vbs script like the one below to remove old files:
http://cwashington.netreach.net/depo/view.asp?Index=723

]]>