As a database professional, I get asked to review the health of database environments very often. When I perform these reviews, one of the many checks I perform is reviewing backup history and making sure that the backup plans in place meet the requirements and service level agreements for the business. I have found a number of backup strategies implemented using full, differential and transaction log backups in some fashion.
In more cases then I would like to share, I have found business critical databases that are not being backed up properly. This could be in the worst case having no backups or a backup strategy that does not meet the recoverability requirement of the business.
When doing an initial check I gather many details about the environment. Regarding backups, I capture things such as recovery model, last full backup, last differential, and the last two transaction log backups. Having this information will allow me to determine what the backup strategy is and point out any recover-ability gaps.
Some examples I have found are 1) no backup’s period, 2) full backup from months ago and daily differentials. In this case the full had been purged from the system, 3) Full backup of user database in Full recovery mode with no transaction log backups, 4) Proper use of weekly full, daily differential, and schedule transaction log backups – however the schedule was set to hourly and the customer expected they would have no more than 15 minutes of data loss. I am happy to report that I do find proper backup routines that meet the customers’ service level agreement too.
The code I like to use for this check is below.
SELECT DISTINCT a.Name AS DatabaseName , CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel , COALESCE(( SELECT CONVERT(VARCHAR(12), MAX(backup_finish_date), 101) FROM msdb.dbo.backupset WHERE database_name = a.name AND type = 'd' AND is_copy_only = '0' ), 'No Full') AS 'Full' , COALESCE(( SELECT CONVERT(VARCHAR(12), MAX(backup_finish_date), 101) FROM msdb.dbo.backupset WHERE database_name = a.name AND type = 'i' AND is_copy_only = '0' ), 'No Diff') AS 'Diff' , COALESCE(( SELECT CONVERT(VARCHAR(20), MAX(backup_finish_date), 120) FROM msdb.dbo.backupset WHERE database_name = a.name AND type = 'l' ), 'No Log') AS 'LastLog' , COALESCE(( SELECT CONVERT(VARCHAR(20), backup_finish_date, 120) FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' , backup_finish_date FROM msdb.dbo.backupset WHERE database_name = a.name AND type = 'l' ) withrownum WHERE rownum = 2 ), 'No Log') AS 'LastLog2' FROM sys.databases a LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name WHERE a.name <> 'tempdb' AND a.state_desc = 'online' GROUP BY a.Name , a.compatibility_level ORDER BY a.name
Ensuring that you have backups is crucial to any check of a SQL Server instance. In addition to ensuring that backups are being created, validation of those backups is just as important. Backups are only valid if you can restore them.
When I have the opportunity to share my experiences of backup and recovery with people I always like to share about how to backup the tail end of a transaction log and how to attach a transaction log from one database to another in order to backup the tail end of the log. I have created a couple of videos on how to accomplish this that you can view using this like http://www.timradney.com/taillogrestore