Best Practice recommendations for SQL Server Database Backups

Article translations Article translations
Article ID: 2027537 - View products that this article applies to.
Expand all | Collapse all

Summary

Backup is an important component of a sound disaster recovery strategy. Presence of a valid and restorable backup is the last thing you should worry about when the moment comes to execute a real disaster recovery scenario during an emergency downtime. Here are some best practices you can follow to ensure you have a good backup in place:
  1. Make sure you are not storing your backups in the same physical location as the database files. When your physical drive goes bad, you should be able to use the other drive or remote location that stored the backups in order to perform a restore. Keep in mind that you could create several logical volumes or partitions from a same physical disk drive. Carefully study the disk partition and logical colume layouts before choosing a storage location for the backups.
  2. Make sure you have a proper backup schedule established according to the needs of the application and business requirements. As the backups get old, the risk of data loss is higher unless you have a way to regenerate all the data till the point of failure.
  3. Make sure to actually restore the backups on a test server and verify that you can restore with all the options and conditions you need to use during a planned or un-planned downtime.
  4. Use the verification options provided by the backup utilities [BACKUP TSQL command, SQL Server Maintenance Plans, your backup software or solution, etc].
  5. Use advanced features like BACKUP CHECKSUM to detect problems with the backup media itself.

More information

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:


Collapse this tableExpand this table
Rule software



Rule title



Rule description



Product versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)





































Database files and backups exist on the same volume

backups outdated for databases



































The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides rules to detect situations where some of these backup recommendations are not followed. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2.



If you run the BPA tool and encounter an Error with the title of Database Engine - database files and backups exist on the same volume, and then you need to verify the location where you store the backups for your databases. You will encounter this error if the backups are stored in the same location as the database files. If you get this error, there are a couple of important points to consider:
  1. This rule checks only logical volumes of the location for the backup file and the database file. You need to manually ensure that these logical volumes are actually on separate physical disks or drives.
  2. You could encounter this error from the BPA tool when there are old entries present in the backup history tables that shows backup was taken to the same volume as the database files. If you are aware of such backups, you could either ignore the error or clean up the old information from the msdb backup history tables using the stored procedure sp_delete_database_backuphistory.
  3. You could encounter this error if you have your database files and backup files located on network locations or shares. Currently the BPA tool evaluates the first 3 characters of the physical path to find out the drive name.
For more information about the system tables used by the BPA tool to find the location of the backups, refer to the Books Online topic: Viewing Information About Backups

If you run the BPA tool and encounter an Error with the title of Database Engine - backups outdated for databases, then you need to verify the schedule of the backups for your databases and ensure the backups are happening at the schedule you intended it to happen. You will encounter this error if you have not performed a backup of your database in the last one day.

For more information about the system tables used by the BPA tool to find the schedule of the backups, refer to the Books Online topic: Viewing Information About Backups
SQL Server 2008
SQL Server 2008 R2







































SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)



































Database files and backups exist on the same volume

backups outdated for databases 

































The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides rules to detect situations where some of these backup recommendations are not followed.

If you run the BPA tool and encounter an Error with the title of Database Engine - database files and backups exist on the same volume, and then you need to verify the location where you store the backups for your databases. You will encounter this error if the backups are stored in the same location as the database files. If you get this error, there are a couple of important points to consider:  
  1. This rule checks only logical volumes of the location for the backup file and the database file. You need to manually ensure that these logical volumes are actually on separate physical disks or drives.
  2. You could encounter this error from the BPA tool when there are old entries present in the backup history tables that shows backup was taken to the same volume as the database files. If you are aware of such backups, you could either ignore the error or clean up the old information from the msdb backup history tables using the stored procedure sp_delete_database_backuphistory.
  3. You could encounter this error if you have your database files and backup files located on network locations or shares. Currently the BPA tool evaluates the first 3 characters of the physical path to find out the drive name.
For more information about the system tables used by the BPA tool to find the location of the backups, refer to the Books Online topic: Viewing Information About Backups


If you run the BPA tool and encounter an Error with the title of Database Engine - backups outdated for databases, then you need to verify the schedule of the backups for your databases and ensure the backups are happening at the schedule you intended it to happen. You will encounter this error if you have not performed a backup of your database in the last one day.

For more information about the system tables used by the BPA tool to find the schedule of the backups, refer to the Books Online topic: Viewing Information About Backups
SQL Server 2012








































Properties

Article ID: 2027537 - Last Review: May 7, 2014 - Revision: 4.0
Applies to
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2014 Developer
  • Microsoft SQL Server 2014 Enterprise
  • Microsoft SQL Server 2014 Express
  • Microsoft SQL Server 2014 Standard
  • Microsoft SQL Server 2014 Web
Keywords: 
KB2027537

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com