Latest Backups with TSQL

After rebooting a SQL Server, for whatever reason, it is always good to confirm that the system is running good.  There are many things that I check, one of which is to confirm that backups are running.

You can always go to the server browse to the various directories holding backups, and check on the latest files.  This can be difficult for servers with many databases.

Here is my quick check to confirm which backups are running on SQL Server. The query below gives you the 20 most recent backups that have been run.

SELECT TOP 20 database_name,
FROM   msdb.dbo.backupset
ORDER  BY backup_set_id DESC;

In order to get the exact results you are looking for it may require you to add a where clause to filter on specific databases, or to expand the number of results in the TOP statement.

I hope that you find this as useful as I do.

You can also check this with charts and additional details using the Database Health Reports project.

Backup Set Report added to FREE Server Health Reports

In preparation for an upcoming SQL Saturday presentation I have created another report to add to the FREE SQL Server Health reports project.

This report has been replaced with the Backup Report in the Database Health Project.

The Backup Set Report has been added to the SQL Health Reports.  The Backup Set Report  is the 7th report to be added to the Database Health Reports package.

The Database Health reports package is a FREE collection of several reports that I have created to monitor and maintain the health of your SQL server.

With this report you can quickly check out the sizes and growth of the databases with the 10 largest backups.

Download it and enjoy.  If you have any feedback, please send it my way.

Restoring A Log Chain

This is a followup to my Whats more important than a backup plan article posted a couple of months ago.
When you do a database restore, including log backups, it is very important to get the log chain in the right order, or it won’t work.  To restore a full backup, then log backups, the chain of logs could be hard to sort out if you have dozens or hundreds of log backups to restore.  Your sure don’t want to be hand coding these.
The script below shows how to find the last full backup, then get a list of all of the log backups that have occurred since that last full backup.  To do the restore, you should just follow the order of the output of this script.
Sample Code

DECLARE @dbName VARCHAR(1024);
-- be sure to put your database name on the following line.
SET @dbName = 'YourDatabaseName';

DECLARE @fullbackup_file VARCHAR(2048);

@fullbackup_file = m.physical_device_name,
@bfd = b.backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name = @dbName
AND TYPE = ‘D’ – type D = full database backup
ORDER BY backup_set_id DESC;
SELECT @fullbackup_file AS backup_file_name,
@bfd AS backup_finish_date
SELECT m.physical_device_name AS backup_file_name,
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name = @dbName
AND backup_finish_date > @bfd
AND TYPE = ‘L’ – type L = log backup
ORDER BY backup_finish_date ASC;

Donwload the sample code ShowRestoreChain.

The output will look something like this…

Once you have the list of files that need to be restored you can then just format it into a sql script to do the full restore.

Keep in mind that if your database has just failed, you are not going to have access to this information, so I suggest that you script this and save it out as a sql script in the same directory as your backup files so that it can be used for the restore.  This process will be covered in a later posting.

You can also easily get this through the backup report in the new Database Health Reports project.

Whats more important than a backup plan?

A restore plan of course is far more important than the backup plan.

When things go wrong and you need to restore a database backup, that is not the time to be learning how to do a restore, or to find out if you were backing up the right data.

My suggestion if your backups are important (why wouldn’t the be), then you should practice doing a restore on a regular basis.  How often is often enough?  Well that depends on your data, and your familiarity with the restore process.

I like to do a restore on at least a weekly basis, but depending on the data I may do a restore on a daily basis.  Also, when doing a restore, don’t just do a full restore, do a full plus logs, this way you will be practicing the same process that you will need to be using when a real failure occurs.

I have talked with many DBA’s who know how to do a restore of a full backup, but who have never restored a full then applied the transaction logs.

The restore of the backups plus transaction logs is simple if you practice it and have it scripted.  It is extremely complex and hard to follow if it is your first time doing it.


My suggestion, if you have never done a restore with transaction logs, then do it now, and practice it weekly until you are good at it, then practice it monthly at least.


Remember databases never fail at convenient times, and when there is a database failure all of management will be waiting on you to fix it.  Be Prepared.