How to determine backup information in SQL 2005

<< Rebuild Master Database in SQL 2005 | Home | How to Setup Visual Studio 2005 to use the Microsoft Symbol Server >>

How to determine backup information in SQL 2005

Another question I got at the denver launch was how can someone check to see if a database (in SQL 2005) was backed-up or not. There is a couple of ways you can get this information

Use the SQL Management Studio. Open the studio, right click on the database in question, choose restore, restore database... On the general tab you will see the backup sets associated to this database.
If you have a backup file and want to get more information, you can use commands RESTORE FILELISTONLY, RESTORE HEADERONLU, RESTORE LABELONLY. Please refer to books online to get detailed information about these commands.
Go and query the MSDB tables directly.MSDB contains all the information you will need about the what backups have ran. for instance, to query all the backup sets for a database called “FOO“ you could execute “Select * from backupset where database_name='foo'“. Other tables you might be interested in are: Backupfile, Backupfilegroup, Backupmediafamily, Backupmediaset, Backupset. Complete descriptions of these tables can be found in books online.

Print posted @ Saturday, December 17, 2005 11:18 AM

Feedback

# re: How to determine backup information in SQL 2005

Run below command set to get list of all full backup till date
============================================

DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT bup.user_name AS [User],
bup.database_name AS [Database],
bup.server_name AS [Server],
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
AS [Total Time]
FROM msdb.dbo.backupset bup
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
WHERE bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
and type='D'
ORDER BY bup.backup_start_date desc 8/14/2009 5:21 AM | nikhil

Tom Wisnowski