Efficiently monitoring your database backups is crucial for your system, as without these reports you won’t be able to restore your system in case of data loss or data corruption. You also wouldn’t be to follow changes in your data over time. For these reasons alone, you must make sure that all of your databases are backed up regularly, each by a service that is best matched to the database’s role in the system and to your organizational needs.
SQL Server Maintenance Plans or third party software are currently the most popular ways to create backup plans. Third party software is also used for monitoring the database.
The following are the most common backup devices:
- local hard drive
- backup tape
- network device
In order to verify that the backup finished successfully most DBAs set an automatic email to be sent to them in case of a failure in the job and they regularly check the automatic emails sent to them so that they know when a backup process fails. Though this is the most common practice, it is not the best practice because a backup process might be halted for any number of reasons. In such a case there would be neither a successful finish notification nor a failure notification. Consequently, it is vital that DBAs verify the success of their backups independently, without relying on automatic reports.
As yourself these questions to make sure that your database backup finished successfully:
- Do my database backups match my backup plans and policies?
- Where are my backups and where do I backup each database to?
- What is my backup type and what was the recovery mode of my database during the backup process?
- Who ran the backup? Was it my service or maybe an SQL injection stealing my data?
- What were the times, durations and sizes of each backup?
- Where can I get alerts when the backup size is abnormal or when the duration is out of the ordinary?
- Did I run the backup during the middle of the day (FULL BACKUP) within working hours?
- If I run several backup operators (Backup local, Network Path, Backup Tape, Virtual SNAPSHOT), are any of them blocked? Do they block each other?
- Did I backup the Log Database using a different operator? We need all log files in order to be able to restore successfully.
Normally, in order to check all of the above you’d need to query MSDB. The MSDB system database is the primary repository for storage of SQL Agent, backup management, Service Broker, Database Mail, Log Shipping, restore manager, and maintenance plan metadata. Here are a few tables that document the backup process:
- backupset: provides information concerning the most-granular details of the backup process
- backupmediafamily: provides metadata for the physical backup files as they relate to backup sets
- backupfile: this system view provides the most-granular information for the physical backup files
Using the following script you can get basic results regarding your backups
–This is an example of getting information about Database Backups for all databases during the previous 3 days:
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 3)
Of course, the code above was a specific example for a specific case – if you’d like to get more concrete information or filter your information to specific database(s), backup type, back-up device or day(s), etc. you’ll have to alter your query accordingly.
Using AIMBETTER you can get all of this information and relevant alerts.
A few screenshots that illustrate the problems we listed are as below:
Optional filters
Detailed results
Alerts on missed backups
AimBetter enables you to see all the backup information you request in a simple way and on one screen so that you won’t have to remember queries by heart in order to efficiently monitor your backups, which makes it a more efficient and more convenient alternative to today’s outdated (albeit most common) backup practice.