SA0086 : Avoid storing database backups on the same volume as the database’s data files

When a database stores both its data and backup files on the same physical disk, it increases the risk of total data loss. This vulnerability arises because a physical failure of the disk would result in both the original data and its backups becoming unavailable. It is crucial for database administrators to ensure that data and backup files are stored on different physical drives to prevent such catastrophic failures.

For example:

1-- Example of potential disk setup risk
2-- Both database and backups on the same drive
3-- Suppose C: is being used for both data and backups

In this example, storing both the database data files and their corresponding backup files under the same drive (e.g., C:) exposes the setup to a single point of failure. In case of drive failure, recovery becomes impossible without external backups.

• Loss of both live data and backups if the disk fails.

• Increased downtime and potential business impact due to the lack of distributed storage for recovery.

To mitigate the risk of data loss due to the co-location of data and backup files in SQL Server, it is essential to separate these files onto different physical drives.

Follow these steps to address the issue:

1. Identify the current storage locations of your database files and backup files using sp_helpdb or by querying the system views like sys.master_files.

2. Select a separate physical volume or drive for storing your backup files, ensuring it is distinct from the drive used by your data files.

3. Update your backup strategy to specify the new location. You can modify scheduled jobs or use SQL Server Management Studio (SSMS) to set the new path for backups. Here’s an example:

4. Ensure that the new backup location is included in your regular maintenance and backup verification processes.

For example:

1-- Example of specifying a new backup location
2BACKUP DATABASE YourDatabaseName 
3TO DISK = 'D:SQLBackupsYourDatabaseName.bak';

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Maintenance Rules, Bugs

Backup Devices

Optimizing Backup and Restore Performance in SQL Server

Backup and Restore Strategies in SQL Server

Other Resources