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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted January 2, 2018

WEBINAR:
Live

Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js


Date: 1/31/2018 @ 2 p.m. ET

Has Your Database Been Backed up in the Last 24 Hours?

By Greg Larsen

As a DBA your #1 responsibility is to make sure that each of your SQL Server databases are backed up.  Without recent backups you will not be able to recover your database without potentially losing many days of database updates.  Therefore, you need to make sure on a daily basis that your backups are running and running successfully.  In this tip, I provide a couple of TSQL scripts that will identify the last backup of each database.  By reviewing the output from these scripts, you will be able to identify the last backup for the databases on your instance.

Script #1:  Return the LastBackupDateTime for each database on your Instance

SELECT db.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(19), MAX(bs.backup_finish_date), 120),'None') AS LastBackUpDateTime
FROM sys.sysdatabases db
        LEFT OUTER JOIN msdb.dbo.backupset bs 
     ON bs.database_name = db.name
GROUP BY db.Name; 

Script #2: Show the databases that have never had a backup, or the current backup is over 24 hours old

SELECT db.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(19), MAX(bs.backup_finish_date), 120),'None') AS LastBackUpTime
FROM sys.sysdatabases db
        LEFT OUTER JOIN msdb.dbo.backupset bs 
     ON bs.database_name = db.name
GROUP BY db.Name
HAVING max(bs.backup_finish_date) < dateadd(dd,-1,getdate())
    or max(bs.backup_finish_date) is NULL;

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.