Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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,


share|improve this question

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.

share|improve this answer

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,
       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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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