SQL Server Performance Forum – Threads Archive

what i m missing in Restore

Hi Champs!! I have a db for that i took full backup daily, and trans backups after every 15 mins. now tran backups are getting append into one physical file, while i am executing restore commands its picking up only first tran backup and applying it on db. how can i restore all tran backups those have been appended into tranbackup file. just for test: – created test db
insert into test values( ‘deepak’,’ats’,27) inserted 20 times and took full backup
again inserted 40 times and took tran backup = total count to records 60
again inserted 40 times and took tran backup = total count to records 100
again inserted 40 times and took tran backup = total count to records 140 — i am restoring db with tran backup Restore database [test1]
FROM DISK=’d: est.Bak’ WITH NORECOVERY, REPLACE,
MOVE ‘test_data’ TO ‘d: est_Data.MDF’,
MOVE ‘test_Log’ TO ‘d: est_Log.LDF’
RESTORE LOG [test1]
FROM DISK=’d: esttran.Bak’
WITH RECOVERY
this recover database till 60 records only. what is the way to apply all tran backups on database, without knowing how many times sql job have taken tran backups after full backup?? Deepak Kumar –An eye for an eye and everyone shall be blind

this can be done using manual commands.. but how would i come to know how many times i should write restore log commands.. in case my phsical server got crashed and can not read sql error logs/ windows events or can’t access enterprises manager…??? Restore database [test333]
FROM DISK=’d: est.Bak’ WITH NORECOVERY, REPLACE,
MOVE ‘test_data’ TO ‘d: est333_Data.MDF’,
MOVE ‘test_Log’ TO ‘d: est333_Log.LDF’ RESTORE LOG [test333] FROM DISK = N’D: est_tran.bak’ WITH FILE = 1, NOUNLOAD , STATS = 10, noRECOVERY
RESTORE LOG [test333] FROM DISK = N’D: est_tran.bak’ WITH FILE = 2, NOUNLOAD , STATS = 10, noRECOVERY
RESTORE LOG [test333] FROM DISK = N’D: est_tran.bak’ WITH FILE = 3, NOUNLOAD , STATS = 10, RECOVERY
Deepak Kumar –An eye for an eye and everyone shall be blind

I did R&D and found, just a single loop was required.. Now i don’t care to find number of completed tran backups from anywhere. So if i don’t have any source of information can blindly run below loop and after that recovery statement. Restore database [test3333] FROM DISK=’d: est.Bak’ WITH NORECOVERY, REPLACE, MOVE ‘test_data’ TO ‘d: est3333_Data.MDF’, MOVE ‘test_Log’ TO ‘d: est3333_Log.LDF' declare @count int set @count= 1 while @count < 100 begin RESTORE LOG [test3333] FROM DISK = N’D: est_tran.bak’ WITH FILE = @count, NOUNLOAD , STATS = 10, noRECOVERY set @[email protected]+1 end restore database test3333 with recovery <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Deepak Kumar –An eye for an eye and everyone shall be blind

]]>