SQL Backup Storage Policy Configuration: Best Practice vs. Real-World Constraints

Hi ​@Nik 

Running SQL backups with Data and Logs pointing to different storage policies can indeed pose risks, especially if one MediaAgent goes offline.

Separate Policies for SQL Backups

Risks:

-- Restore Failures: If one MediaAgent is offline, restores could fail because the Full backup depends on the Tlog backup1.
-- Complexity: Managing separate policies can add complexity to your backup and restore processes.

Handling the Risk:

-- Redundancy: Ensure redundancy by having multiple MediaAgents and configuring failover paths.
-- Monitoring: Regularly monitor the health of your MediaAgents and storage policies to quickly address any issues.

Single Storage Policy for SQL Backups

Benefits:

-- Simplicity: Using a single storage policy simplifies management and reduces the risk of restore failures1.
Consistency: Ensures that all backups (Full, Differential, and Tlog) are stored together, making restores more reliable.

Best Practices:
-- Retention Settings: Configure appropriate retention settings to manage storage space effectively.
Regular Testing: Regularly test your backup and restore processes to ensure they work as expected.

"Honor SQL Chaining for Full Jobs" Setting

Functionality:

This setting ensures that Full backups honor the chaining of SQL transaction logs, which can help maintain the integrity of your backups
When enabled, Full jobs on selective copies retain logs that are chained, preventing them from being aged prematurely2.
Considerations:

Impact on Storage: Enabling this setting might increase storage usage as logs are retained longer.
Testing: Test this setting in a non-production environment to understand its impact on your backup and restore processes.
Recommendations
Evaluate Your Environment: Assess your current setup and the criticality of your SQL databases to decide whether to use separate or single storage policies.

Refer to Commvault's documentation and community forums for best practices and real-world experiences

https://community.commvault.com/self-hosted-q-a-2/sql-backup-storage-policy-configuration-best-practice-vs-real-world-constraints-10179

https://documentation.commvault.com/11.38/expert/define_storage_and_retention_for_backups_using_storage_policies.html

https://community.commvault.com/self-hosted-q-a-2/ms-sql-transaction-logs-not-expiring-even-though-retain-until-time-has-passed-3202