« All articles

Backup Monitoring

It is important to regularly monitor database backups and ensure that no scheduled backups are missing. A backup’s absence means there is a time frame that is not recoverable, and therefore if there has been a serious failure shortly after the time the backup was supposed to take place we will not be able to restore the system to its state shortly before the incident and at best we could restore it to some more time earlier, which means we lose the changes occurred since the last backup we have and therefore we would need to redo all our actions since then. At worst, if we have no backups stored for a specific database it means that we cannot restore the system to the way it was before the failure in a simple manner or at all, therefore it’s important to make sure that all databases are backed up according to a backup plan that best matches to the system’s best practice and to the Recovery mode of each database.

There are 3 backup types used by SQL Servers, Full Backup. Differential Backup and Transactional Log Backup:

Full backup

A full backup contains all the data in a specific database or set of file groups or files, and also enough log to allow for recovering that data. It is the base of both differential backup and transaction log backup.

Differential backup

A differential backup is not independent and it must be based on the latest full backup of the data. That means there should have a full backup as a base. A differential backup contains only the data that has changed since the differential base. Typically, differential backups are smaller and faster to create than the base of a full backup and also require less disk space to store backup images.
Therefore, using differential backups can save available space and speed up the process of making frequent backups to decrease the risk of data loss. At restore time, the full backup is restored first, followed by the most recent differential backup.
Remember not to rely on only one base, create several base of differential backup at different times to guarantee the backup files’ security.

Transaction Log Backup (Full and Bulk-Logged Recovery Models Only)

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.

The transaction log backups are only valuable under the full recovery model or bulk-logged recovery model. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup. An uninterrupted sequence of log backups contains the complete log chain of the database, which is said to be unbroken. Under the full recovery model, and sometimes under the bulk-logged recovery model, an unbroken log chain lets you to restore the database to any point in time.
Just like differential backup, transaction log backup is also based on full backup. Therefore, before you can create the first log backup, you must create a full backup, such as a database backup. Because it requires less disk space than full backup, you can create them more frequently than database backups.

In order to know the last successful backup of each db you should run the following query:

SELECT sd.name,
bs.TYPE,
bs.database_name,
max(bs.backup_start_date) as last_backup
FROM master..sysdatabases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id =bmf.media_set_id
Group by sd.name,
bs.TYPE,
bs.database_name
Order by sd.name,last_backup
In order to view all the existing backups of a specific database (DBA_Info in this example) you should run the following query:
SELECT sd.name,
bs.TYPE,
bs.database_name,
bs.backup_start_date as last_backup
FROM master..sysdatabases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) =rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id =bmf.media_set_id
WHERE sd.name = 'DBA_Info' and bs.backup_start_date > getdate() - 10
Order by sd.name,last_backup

In the result set you’ll notice by the pattern if there are any missing backups.
Using the scripts above you could easily monitor your backups and know immediately if your backup policy is applied properly.

Leave a Reply

Your email address will not be published.