In one of my very first posts (https://sqlbadboy.wordpress.com/2012/09/25/what-should-i-be-doing-as-a-dba/), I indicated that one of the DBA’s primary responsibilities is to protect their company’s data by implementing a backup strategy. And because everyone knows this is part of a DBA’s job, the statuses of database backups are always of interest to managers, directors, clients, and even auditors. So, you have to figure that someone will ask about them from time to time!
However, the way you answer the question about the status of your database backups will depend on what your backup strategy is. Many times you will be asked a generic question such as, “Are all the databases being backed up?” The answer to this question should always be yes. But, what they really mean is, is the data protected? And that could be a totally different answer.
For example, imagine a backup strategy where there are daily full backups and hourly log backups. The full backups could be succeeding, but all the log backups are failing for some reason. Are the databases being backed up? Yes. Is the data being protected? No, there could be data loss.
So, how can a better answer be given? One thing that will be helpful is being able to understand the system tables in the msdb database that record the backup history information in SQL Server.
- Backupmediaset (http://msdn.microsoft.com/en-us/library/ms189513) – This table gives details about the backup, such as how many file(s) were used, what program did the backup, is it password protected, compressed, or encrypted (SQL Server 2014).
- Backupmediafamily (http://msdn.microsoft.com/en-us/library/ms190284.aspx) – This table indicates the names of the physical backup file(s) and what type of device (i.e. disk, tape, etc.) was used. It can be joined to the backupmediaset table via the media_set_id.
- Backupset (http://msdn.microsoft.com/en-us/library/ms186299.aspx) – This table contains details for each successful backup, such as when it expires, the user_name that performed the backup, what log sequence numbers (lsn) were backed up, when did the backup begin and end, what type of backup (i.e. database (full), differential, log, filegroup, partial, etc.), settings and versions for the server and database, and database sizes (compressed and uncompressed). It can be joined to the backupmediaset and backupmediafamily tables via the media_set_id.
- Backupfile (http://msdn.microsoft.com/en-us/library/ms190274.aspx) – This table describes the file configuration of the database at the time the backup was taken. The is_present column identifies if the file is included in the backup set. It can be joined to the backupset table via the backup_set_id.
- Backupfilegroup (http://msdn.microsoft.com/en-us/library/ms188759.aspx) – This table shows the filegroup configuration of the database. This table can be used in conjunction with the backupfile table so see whether a filegroup is included in the backup set. It can be joined to the backupset and backupfile tables via the backup_set_id.
Here’s some example code that shows the following:
- Each database on the server
- The last time the database was backed up, with start and finish times
- Type of backup (i.e. full, differential, log, etc.)
- Where the backup file is located (note: there will be multiple records if multiple backup files are used)
- Backup sizes and compression ratio
- Recovery model
SELECT da.name AS database_name, bs.backup_start_date, bs.backup_finish_date, CASE WHEN bs.type = 'D' THEN 'FULL' WHEN bs.type = 'L' THEN 'LOG' WHEN bs.type = 'I' THEN 'DIFFERENTIAL' END AS backup_type, bmf.physical_device_name, CAST(bs.backup_size / 1024. / 1024. AS NUMERIC(10,2)) AS total_backup_size_mb, CAST(bs.compressed_backup_size / 1024. / 1024. AS NUMERIC(10,2)) AS total_compressed_backup_size_mb, CAST(bs.compressed_backup_size / 1024. / 1024. / bms.media_family_count AS NUMERIC(10,2)) AS compressed_backup_file_size_mb, CAST(bs.compressed_backup_size / bs.backup_size AS NUMERIC(4,2)) AS compression_ratio, bs.recovery_model FROM master.dbo.sysdatabases da LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = da.name AND bs.backup_start_date IN ( SELECT MAX(backup_start_date) FROM msdb.dbo.backupset WHERE database_name = bs.database_name AND type = bs.type) LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id LEFT OUTER JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id ORDER BY bs.database_name, bs.backup_start_date, bmf.physical_device_name
Although this code gives a lot of general information, it can be used to dig deeper into whatever is needed regarding the backup history and your backup strategy. The following questions could be answered from the results of this query:
- Do you have a current full backup based on your strategy; perhaps you need a full backup every day or every week?
- Do you have current log backups?
- Do you have a database in the FULL recovery model?
- Are the timings for your backups inside the window allocated? Should you backup to more files to speed up the total time?
- Are you using compression to minimize the backup file sizes?
Since at some point you will have to answer a question about the status of your backups, it is a good idea to get familiar with the tables in msdb which relate to the backup history.