April 30, 2012 at 9:20 am
Hi!
Scenario:
I have a SQL Server 2008 R2 RTM Backup file with multiple backups contained within it.
Inside the backup file (x):
Position=1, Full Backup
Position=2, Differential Backup
Position=3, Transaction Log Backup
My restore commands:
RESTORE DATABASE y
FROM DISK='x'
WITH STANDBY='C:\Standby', FILE=1
GO
RESTORE DATABASE y
FROM DISK='x'
WITH STANDBY='C:\Standby', FILE=2
GO
RESTORE DATABASE y
FROM DISK='x'
WITH STANDBY='C:\Standby', FILE=3
GO
All commands gives seemingly valid output and the restored database seems to contain the correct data.
According to my understanding, restoring the last backup (the Transaction Log backup) should require a RESTORE LOG and not a RESTORE DATABASE.
Anyone knows why this seems to work?
Regards,
Hans
April 30, 2012 at 10:05 am
I haven't tried it myself, but it's entirely possible that Restore Log is only necessary if you're using options like stopping at a certain point in time. Haven't tried it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2012 at 11:29 am
Hans Lindgren (4/30/2012)
should require a RESTORE LOG and not a RESTORE DATABASE.Anyone knows why this seems to work?
That's because the command that will run is a log restore, i ran the following
restore database sql2 from disk =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\Backup\sqloverview.bak'
with norecovery, file = 1,
move 'SQLOverview' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\DATA\sql2.mdf',
move 'SQLOverview_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\DATA\sql2.ldf'
restore database sql2 from disk =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\Backup\sqloverview.bak'
with norecovery, file = 2
restore database sql2 from disk =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\Backup\sqloverview.bak'
with norecovery, file = 3
On the 3rd restore command this was the output
Processed 0 pages for database 'sql2', file 'SQLOverview' on file 3.
Processed 22 pages for database 'sql2', file 'SQLOverview_log' on file 3.
RESTORE LOG successfully processed 22 pages in 0.091 seconds (1.872 MB/sec).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 2, 2012 at 2:46 am
Thanks guys.
Ok, so RESTORE DATABASE currently works when restoring transaction log backups (I also tested using the STOPAT parameter)...
This is not something I have seen documented anywhere and I fear it might become unuseable at any time.
Anyone else have seen any documentation that implies that RESTORE DATABASE would work for Transaction Log backups?
Regards,
Hanslindgren
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply