SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Find databases without backup

By Angel Gómez,

One of the most important and delicate tasks of any DBA is the conduct and control of the backup of all databases in their environments, more so in the production environment.
When we have a great environment with hundreds / thousands of bbdd housed in dozens of instances, it is physically impossible to control the performance of the backup, beyond waiting for notifications maintenance plans alert us.
There may be cases where maintenance plans, or our backup software is not able to tell if the backup of any bbdd has been done, we can find the failure of our backup software, backup disks full, or just have bbdd forgotten to put this new production in our maintenance plan for performing backup of it.
Solution
By consulting the table msdb.dbo.backupset we can get a list of when the last backup of each bbdd was performed, and if the backup does not match our backup plan, we will receive an email letting us know what are the bbdd affected.<p>
Furthermore, if this query the implant in a scheduled job, we can review whether all our bbdd have a full backup with the desired periodizad, otherwise the SQL Server Agent send us an email with a list of databases that have no backup when it should already have it.
Currently, in our environment it is implemented with a daily review of the backup looking for bbdd who have no FULL backup on the last day. 

Total article views: 583 | Views in the last 30 days: 2
 
Related Articles
FORUM

Backup Maintenance Plan Error

Backup Maintenance Plan Error

FORUM

Differential Backup Failures

Scheduled maintenance plan backup

BLOG

Optimizing Backup Performance

If your environment is anything like mine, backups are usually not allocated the speediest disk. Gen...

FORUM

backup on maintenance plan doesn't work.

this is about backup and maintenance plan.

ARTICLE

Maintenance Routines for AlwaysOn

How to determine what replica(s) to perform maintenance routines

Tags
alert    
backup    
script    
 
Contribute