SA0220 : Creating backups with PASSWORD or MEDIAPASSWORD option is deprecated

The topic describes the SA0220 analysis rule.

Message

Creating backups with PASSWORD or MEDIAPASSWORD option is deprecated

Description

The rule checks T-SQL code and reports usages of the deprecated BACKUP { DATABASE | LOG } WITH [MEDIA]PASSWORD option.

Beginning with SQL Server 2012 the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.

How to fix

There is no direct replacement available from Microsoft against the removal of the feature, but the more secure encrypted with a certificate backups can be used as an alternative to this feature.

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
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