Restore Transaction Log Backups

Hy Guys,

I have a problem with restore Log.

We make a FULL backup everyday at 22:00.
Transaction LOGs Backups are made at every 30 minutes (all the day) (WITH NO INIT) - One Log Backup per Day

I restored a a FULL Backup database from 10/17/2002 with this code:

RESTORE DATABASE dbTest
from disk = 'd:\MSSQL7\BACKUP\DB_20021017_dbsolomonprodapp.bak '
WITH norecovery
GO

Now I need to restore Transactions until 10/18/2002 16:30.
So, I have to restore
Lg_20021017_dbSolomonProdApp.bak
AND
Lg_20021018_dbSolomonProdApp.bak (Until 16:30)

My problem is.. How to make this Log Restore?Q1 My problem is.. How to make this Log Restore?
A1 Sequentially (in your case, by position similar to when restoring from tape, using time constraints as necessary). I suggest the following:
i I find it best to Dump to individual (dump) files. (multiple reasons)
ii Doing so also makes it clearer to see what you are doing, for example:

Restore Database
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_db_200210250 830.Bak'
With Stats = 1, Replace, NoRecovery,
Move 'dbSolomonProdApp_Data' To 'c:\Test_dbSolomonProdApp_Data.mdf',
Move 'dbSolomonProdApp_Log' To 'c:\Test_dbSolomonProdApp_Log.ldf'

--> Sequential dTL Restore Statements:

--> dTL file: 1
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50843.Trn'
With Stats = 1, NoRecovery

--> dTL file: 2
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50913.Trn'
With Stats = 1, NoRecovery

--> dTL file: 3
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50943.Trn'
With Stats = 1, NoRecovery

--> dTL file: 4
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51013.Trn'
With Stats = 1, NoRecovery

--> dTL file: 5
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51043.Trn'
With Stats = 1, NoRecovery

--> dTL file: 6
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51113.Trn'
With Stats = 1, NoRecovery

--> dTL file: 7
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51143.Trn'
With Stats = 1, NoRecovery

--> dTL file: 8
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51213.Trn'
With Stats = 1, NoRecovery

--> dTL file: 9
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51243.Trn'
With Stats = 1, NoRecovery

--> dTL file: 10
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51313.Trn'
With Stats = 1, NoRecovery

--> dTL file: 11
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51343.Trn'
With Stats = 1, NoRecovery

--> dTL file: 12
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51413.Trn'
With Stats = 1, NoRecovery

--> dTL file: 13
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51443.Trn'
With Stats = 1, NoRecovery

--> dTL file: 14
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51513.Trn'
With Stats = 1, NoRecovery

--> dTL file: 15
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51543.Trn'
With Stats = 1, NoRecovery

--> dTL file: 16
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51613.Trn'
With Stats = 1, NoRecovery

--> dTL file: 17
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51643.Trn'
With Stats = 1, NoRecovery

--> dTL file: 18
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51716.Trn'
With Stats = 1, NoRecovery

--> dTL file: 19
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51743.Trn'
With Stats = 1, NoRecovery

--> dTL file: 20
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51813.Trn'
With Stats = 1, NoRecovery

Restore DataBase
[Test_SolomonProdApp]
With Recovery|||Hy DBA,
Your answer works perfect. But i was with my transaction logs in one file. Then i made like this:

-- This command returns many important information about this backup.
-- Like the date of the backup sets and their numbers.
RESTORE HEADERONLY FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' WITH NOUNLOAD
GO

--Restoring the last Full Backup
RESTORE DATABASE dbSolomonTeste
from disk = 'd:\20021016_dbsolomonprodapp.bak'
WITH norecovery
GO

--Now I recovery all backup sets in the Log backup
RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 2,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 3,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO
.
.
.

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 48, --In my Case, the last backup set i needed!
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

Thank you for helping me!!|||RE: Thank you for helping me!!

You are welcome.

As I alluded to, I generally avoid monolithic TL dump files.
Some pain I've experienced with them includes:
i) You want to move it elsewhere to use it, but cannot because it has become huge (either connectivity loss, corruption, etc., occurs during attempted file transfer processes, or the target only has sufficient free disk space on multiple smaller volumes).
ii) RESTORE HEADERONLY results take FOREVER, and fails before the complete header result set is returned (fortunatly, the needed TL dumps on it were usable).
iii) It (a single monolithic TL dump file) gets corrupted, and the whole thing is useless! (That can be extremely painful for an organization, and is the main reason I implement and / or encourage the use of individual dump files.) While I have seen this happen on a Windows OS fileserver, it seems to happen most frequently on third party NAS boxes, (especially older SNAP storage servers), with large monolithic TL dump files. I don't recall ever seeing it happen on a local data storage volume, however; so, if that is your situuation, you may have relativly little (corruption) risk in using monolithic TL dump files.