Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Which Databases are Being Backed Up - Another Look

By Santveer Singh,

I was reading the article Every DBA Should Know Which Databases are be Backed Up by Leo Peysakhovich. I agree with Leo, that most of the time when we need a backup of database, we noticed that we don't have backup of that database or we have a very old set of backups. To avoid this kind of situation, I have written a view based on system tables which allows me to know when the last time a database was backed up and which database have never been backed up.

Step 1: create  a view in master or any user database using below script.

Create View Last_Backup
as
Select a.name,Backup_Date from master.dbo.sysdatabases a
	left join 
            (select database_name,cmax(backup_finish_date) backup_date
            from msdb.dbo.backupset where backup_finish_date <= getdate()
            group by database_name)  B
	on  a.name = b.database_name
go

Step 2.1: Once this view is ready, to know when databases are last time backed up

Select * from Last_Backup order by 2 desc, 1

Name                               backup_date
-------------------                ---------------------------
SBD_EMIS_VIEWS                     2004-05-25 02:03:11.000  
PILOT_VIEWS                        2004-05-25 02:02:54.000  
PILOT_USER                         2004-05-25 2:02:53.000   
PILOT_SYSTEM                       2004-05-25 02:02:50.000 
PILOT_DATA_FRAME                   2004-05-25 02:02:46.000

Above result shows when database was last backed up.

Step 2.2: To know the databases that were never backed up

Select * from Last_Backup       where backup_date is null

Name                                 backup_date
-------------------                  --------------------------
CG_EMSP                              NULL
CG_FMO_ARCHIVE                       NULL  

Above result shows that two databases were never backed up.

Step 3: Add these missing databases into appropriate backup maintenance plan.

Step 4: We can schedule a job to send email on daily basis to know the backup status of all databases. Attached is the script for the same.

Conclusion

By spending few minutes to know which database was not backed up and update backup plan on regular basis, we can save data & our life.

Total article views: 9335 | Views in the last 30 days: 8
 
Related Articles
FORUM

DataBase Backup for selected tables through SSIS 2005

DataBase Backup for selected tables

FORUM

Database backup

Database backup

FORUM

Selective Backups

How can I make more selective backups?

FORUM

Backup Software Selection

Selecting a Backup Software

FORUM

database backup

database backup

Tags
administration    
monitoring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones