Query to get last database backup for each DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query to get last database backup for each DB

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-12 : 14:24:18

What I am trying to do is get the last database backup for each of the databases. I want the results set to have is the database name and the path + file name of the backup. bs.database_name is the database name and bms.physical_device_name is the path + file name of the backup. bs.backup_finish_date is the date and time that the backup completed.

If you've got any SQL Servers out there with multiple database backups for each of the databases, then you can run this code.

The below query gives me all of the backups for each of the databases. I just want the most current.


SELECT bs.database_name, bms.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind')
ORDER BY bs.database_name, bs.backup_finish_date DESC

This query just gives me one backup per database, but it isn't necessarily the most current one.


SELECT bs.database_name, MAX(bms.physical_device_name) AS Backup_File
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind')
GROUP BY bs.database_name

This query gives me the most current backup that occurred TODAY for each of the databases.
The problem with this one is that I could be running the query on a particular day before the backups have run, so I'll get 0 records returned.



SELECT bs.database_name, MAX(bms.physical_device_name) AS Backup_File
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND 
      s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind')
GROUP BY bs.database_name

Example result set of the first query above (there are only two columns in the result, but I included sample dates to the right):


DB1    E:\MSSQL\BACKUP\DB1_20030510.bak ----(backup occurred May 10, 2003)
DB1    E:\MSSQL\BACKUP\DB1_20030401.bak ----(backup occurred Apr. 1, 2003)
DB1    E:\DB1_Full.bak                  ----(backup occurred Dec. 31, 2002)
DB2    F:\Backup\DB2.bak                ----(backup occurred Jan. 5, 2003)
DB3    E:\DB3_20030612.bak              ----(backup occurred June 12, 2003)
DB3    E:\DB3_20030611.bak              ----(backup occurred June 11, 2003)

Desired result set for the above data:


DB1    E:\MSSQL\BACKUP\DB1_20030510.bak
DB2    F:\Backup\DB2.bak
DB3    E:\DB3_20030612.bak

Tara

Edited by - tduggan on 06/12/2003 14:29:45

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-12 : 16:35:21

Jeff, Brett, Nigel, Bueller, anyone?

This question isn't about the last database backup but rather how to do this in T-SQL. I can't figure out the GROUP BY or if I need to do this in multiple steps.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources