I'm working with SQL Server 2008Sp3.

I have a database that was restored from a full backup WITH NO RECOVERY and it then was restored with Transaction log backup files - also WITH NO RECOVERY.

At the moment the database is in Restoring status.

Let's say that couple of hours after the last transaction log restoration I want to continue restore the new 2 hourly transaction logs that were created, and then restore it WITH RECOVERY.

How can I query the status of the database, so I will know what was the last Transaction log backup that was restored on that database?

The reason I'm asking that is because I would like to automate this in a script -

I want to create a daily full restoration of a database on a separate server with all the available Transaction log backups WITH NO RECOVERY, and then upon request (when it's necessary), I'll be able to execute another script that will restore the new Transaction log backups that were created since the end of the daily script + restore it WITH RECOVERY.

My question is regarding the second separate script - finding the rest of the needed Transaction log backups.

Thanks in advance,


2 Answers 2


I'm not sure if I fully understand your question. First you say you want to find the last restore made to a certain database. You can find all relevant information (databases, files, lsns, times) on these restores through

FROM [msdb].[dbo].[restorehistory] r
INNER JOIN [msdb].[dbo].[backupset] b 
ON b.backup_set_id = r.backup_set_id 

But I'm not sure what you mean with "finding the rest of the needed backups". If you want to look at the backups made in an instance you can do this through

SELECT * FROM [msdb].[dbo].[backupset]

Hope that helps.


I got the information I needed from the following query:

SELECT top 1    bs.database_name,
                CASE bs.type
                    WHEN 'D' THEN 'Database'
                    WHEN 'L' THEN 'Log'
                    WHEN 'I' THEN 'Differential'
                END AS backup_type,
                bs.name AS backupset_name,
FROM msdb.dbo.backupmediafamily as bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE (CONVERT(datetime, bs.backup_start_date,102) >=GETDATE()- 7) 
AND bs.database_name='<DB NAME>'
ORDER BY bs.database_name,bs.backup_finish_date desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.