SA0220 : Creating backups with PASSWORD or MEDIAPASSWORD option is deprecated

The use of deprecated BACKUP WITH MEDIAPASSWORD options in T-SQL code can lead to potential issues when managing SQL Server data.

Description

With the release of SQL Server 2012, the PASSWORD and MEDIAPASSWORD options for creating backups were discontinued. Although restoring backups that were originally created with these options is still supported, continuing to use them in new backup scripts can result in various problems.

For example:

SQL
Copy
1-- Deprecated use of MEDIAPASSWORD in backup command
2BACKUP DATABASE SampleDB 
3TO DISK = 'C:BackupSampleDB.bak' 
4WITH MEDIAPASSWORD = 'abc123';

This example illustrates a backup command using the MEDIAPASSWORD option. While it may function if you’re running on an older version or restoring from an older backup, it is not suitable for new scripts due to its discontinued status.

  • Incompatibility with current and future versions of SQL Server, leading to potential errors during backup operations.

  • Reduced security as these options no longer provide meaningful protection in scenarios involving backups.

How to fix

To ensure future compatibility and enhanced security for SQL Server backups, replace deprecated WITH MEDIAPASSWORD options with encrypted backups using certificates.

Follow these steps to address the issue:

  1. Identify all existing backup scripts that utilize the WITH MEDIAPASSWORD option. Review your T-SQL scripts and SSMS maintenance plans for these occurrences.

  2. Create a new certificate in SQL Server to be used for encrypting the backup. Use the following T-SQL command to generate a certificate:

    SQL
    Copy
    1CREATE CERTIFICATE BackupCert
2WITH SUBJECT = 'Backup Encryption Certificate';

  3. Modify your backup scripts to use backup encryption with the newly created certificate. Replace the deprecated usage with the ENCRYPTION option as demonstrated below:

    SQL
    Copy
    1BACKUP DATABASE SampleDB 
2TO DISK = 'C:BackupSampleDB_Encrypted.bak' 
3WITH ENCRYPTION( 
4    ALGORITHM = AES_256, 
5    SERVER CERTIFICATE = BackupCert 
6);

  4. Test the updated backup process to ensure that backups are created successfully and can be restored as needed. Validate by restoring the encrypted backup on a test database instance.

For example:

SQL
Copy
1-- Example of corrected, secure backup command with encryption
2BACKUP DATABASE SampleDB 
3TO DISK = 'C:BackupSampleDB_Encrypted.bak' 
4WITH ENCRYPTION( 
5    ALGORITHM = AES_256, 
6    SERVER CERTIFICATE = BackupCert 
7);

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
3 hours per issue.
Categories

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
1BACKUP DATABASE Northwind TO DISK='C:Northwind.BAK' WITH MEDIAPASSWORD='mypassword'
2BACKUP DATABASE Northwind TO DISK='C:Northwind.BAK' WITH PASSWORD='mypassword'
3
4RESTORE DATABASE Northwind FROM DISK='C:Northwind.BAK' WITH MEDIAPASSWORD='mypassword'
Analysis Results
  Message Line Column
1 SA0220 : Creating backups with PASSWORD or MEDIAPASSWORD option is deprecated. 1 58
2 SA0220 : Creating backups with PASSWORD or MEDIAPASSWORD option is deprecated. 2 58
See Also

Other Resources

Analysis Rules