Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Updated May 21, 2021 / Posted May 12, 2021

What Backups Do I Have?

By Greg Larsen

The #1 priority of a DBA is to make sure they can recover a database should it become corrupted.  To recover a database, you need to make sure you have backups.

Is knowing you have a backup process that is scheduled to run enough to make sure you have backups? No! I've actually experienced a situation where my backup jobs were running every night, but were not creating the backups correctly. Worse yet, I wasn't notified.

Therefore, as a DBA you need to check periodically to make sure you have backups. I would recommend you do this daily, if you are taking backups daily.

Below is a TSQL script that will identify all the database backups, their start and finish times, and the type of backups that were taken in the last 7 days.

SELECT b.database_name, 



       CASE b.type 

       WHEN 'D' THEN 'Database'

       WHEN 'I' THEN 'Differential'

       WHEN 'L' THEN 'Log'

       END AS backup_type, 


 FROM msdb.dbo.backupmediafamily f

 INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id 

 WHERE b.backup_start_date > GETDATE() - 7

 ORDER BY b. backup_finish_date  desc;


Don’t be caught without a critical database backup.  Have a routine that periodically checks for backups using this script or something similar to the one above, then review the output.  By doing this you can make sure you have backups, so you can recover your database if needed.


Latest Forum Threads
DB2 Forum
Topic By Replies Updated
tables get lock during backup in db2 v9.5 Hamsoo 0 May 8th, 01:03 AM
Query to Pull Last 7 days records from a table vgoushik 0 March 20th, 06:05 PM
DB2 Visual studio addin: ANSI/Unicode problem 10Pints 0 October 5th, 02:08 AM
Things that are everbodys labor day ideas? Lipsett197 0 September 1st, 09:42 AM