How Successful SQL Server Backups Can Fail

You are probably running SQL Server backups, and you might even test that process on a regular schedule to prevent issues during a disaster. Are you confident that the backup can be restored? If you aren’t paying attention to who is doing backups, you might be in for a surprise when you attempt a restore.

Let me give you a sample scenario that you might not have considered. Since your database is quite large, you do a daily full system backup with hourly incremental backups. When it comes time to restore those backups, you restore the last good daily full backup, and all the successful incremental backups since the daily backup was taken to get the system restored to within an hour of the outage. This is a fairly well defined process that you have scripted and tested several times. Along comes a developer that creates a full system backup in the middle of the day to restore a backup to a development or test environment. When you attempt to restore your database, you must have a copy of that developers full backup, not the one you created. Will you be able to find that backup during a disaster?

You should, as a minimum anticipate this scenario and prepare to adjust your restore procedures to account for this type of issue. That might not even be possible, based on the environment at your business. You might need to review your processes and access permissions to prevent this from even happening. Why does the developer need access to conduct backups? Why doesn’t he just use the backups you are already creating?

If you aren’t paying attention, your well thought out recovery plan could be useless when you need it the most.

This is a script from Microsoft to get your database backup history:

      SELECT bs.server_name AS ServerName, 
       CASE bs.compatibility_level  
         WHEN 90 THEN 'SQL Server 2005' 
         WHEN 100 THEN 'SQL Server 2008 or SQL Server 2008 R2'  
         WHEN 110 THEN 'SQL Server 2012'  
         WHEN 120 THEN 'SQL Server 2014' 
       END AS ServerVersion,  
       bs.database_name AS DatabseName, 
       CASE bs.type  
         WHEN 'D' THEN 'Full'  
         WHEN 'I' THEN 'Database Differential'  
         WHEN 'L' THEN 'Log'  
         WHEN 'F' THEN 'File or filegroup' 
         WHEN 'G' THEN 'Differential file' 
         WHEN 'P' THEN 'Partial' 
         WHEN 'Q' THEN 'Differential partial'  
       END AS BackupType, 
       bs.backup_start_date AS BackupStartDate, 
       bs.backup_finish_date AS BackupFinishDate,  
       CASE bmf.device_type  
         WHEN 2 THEN 'Disk'  
         WHEN 5 THEN 'Tape' 
         WHEN 7 THEN 'Virtual device'    
         WHEN 105 THEN 'A permanent backup device'  
         ELSE 'Other Device'  
       END AS DeviceType, 
       bmf.physical_device_name AS PhysicalDevice, 
       bs.backup_size/(1024*1024AS [BackupSize(MB)],  
       bs.compressed_backup_size/(1024*1024AS [ConmpressedBackupSize(MB)] 

The output looks something like this:

You can get more information on this script here.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s