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.
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: 590 | Views in the last 30 days: 1
Related Articles

Backup Maintenance Plan Error

Backup Maintenance Plan Error


Differential Backup Failures

Scheduled maintenance plan backup


Optimizing Backup Performance

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


backup on maintenance plan doesn't work.

this is about backup and maintenance plan.


Maintenance Routines for AlwaysOn

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