April 24, 2009 at 3:24 pm
I have to restore a t-log backup ontop of a restored db. I would like to query for the backup set #'s from the t-log backup file so I can see them to help me on the restore. You can get this from the GUI in Enterprise Manager on Restore Database>View Contents, but does anyone know the tsql equivelant to get the same info?
Appreciate insight.
April 24, 2009 at 3:33 pm
you want restore headeronly.See BOL or
http://msdn.microsoft.com/en-us/library/aa238455(SQL.80).aspx
tip: if you want to know what commands EM issues when you use it, run profilwer filtered for EM events only while you perform the function thru the GUI.
---------------------------------------------------------------------
April 24, 2009 at 3:37 pm
This should give you what I beleieve you are after, you may need to add more feilds, sure I got this from here in the past, can't remember:
select
SUBSTRING(s.name,1,40)AS'Database',
CAST(b.backup_start_date AS char(11)) AS 'Backup_Date ',
b.backup_start_date as Start_Date_Time,
first_lsn, last_lsn,
bf.physical_device_name
from master..sysdatabasess (nolock)
LEFT OUTER JOINmsdb..backupset b (nolock)
ON s.name = b.database_name
and b.type = 'L'
inner join msdb..backupmediafamily bf (nolock)
on b.media_set_id = bf.media_set_id
WHEREs.name = 'DatabaseName'
and backup_start_date between getdate()-1 and getdate()
ORDER BY backup_start_date desc, SUBSTRING(s.name,1,40)
Andrew
April 24, 2009 at 3:37 pm
RESTORE HEADERLISTONLY FROM DISK = '{your tlog backup file here}';
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 26, 2009 at 10:37 am
thnxs, I tried your solution below...
RESTORE HEADERLISTONLY FROM DISK = 'F:\MSSQL\BACKUP\HR83PRD_Log_20090421.bak'
Server: Msg 155, Level 15, State 1, Line 1
'HEADERLISTONLY' is not a recognized RESTORE option.
GO
Am I missing something?
April 26, 2009 at 11:16 am
so try restore headeronly...................:-)
---------------------------------------------------------------------
April 29, 2009 at 8:31 pm
Great, thnxs guys....this definetely helped me.
thnxs again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply