SA0200 : Backup to tape syntax is deprecated

Deprecated syntax for specifying backup devices like TAPE in T-SQL script can lead to compatibility issues and is generally unused in modern environments.

Description

In SQL Server, using the deprecated BACKUP { DATABASE | LOG } TO TAPE syntax can create problems because it relies on old technology. Modern environments typically use disk-based storage or cloud solutions for backups.

For example:

SQL
Copy
1-- Example of deprecated usage
2BACKUP DATABASE MyDatabase TO TAPE = 'MyTapeDevice';

The example above is problematic because:

  • It depends on tape devices, which are becoming obsolete, leading to potential difficulties in maintaining hardware.

  • This syntax may not be supported in future versions of SQL Server, increasing the risk of compatibility issues.

How to fix

To prevent compatibility and maintenance issues, it is recommended to use disk-based storage for backups by replacing the use of deprecated `BACKUP { DATABASE | LOG } TO TAPE` syntax with `BACKUP { DATABASE | LOG } TO DISK` syntax.

Follow these steps to address the issue:

  1. Identify instances of the deprecated BACKUP TO TAPE syntax in your scripts or processes.

  2. Replace the tape backup device with a disk-based solution by using BACKUP TO DISK syntax. Specify a valid file path on your disk storage.

  3. Modify any automated scripts or jobs that use the older syntax to ensure they utilize disk-based backups instead.

For example:

SQL
Copy
1-- Example of corrected query
2BACKUP DATABASE MyDatabase TO DISK = 'C:BackupMyDatabase.bak';

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
20 minutes per issue.
Categories

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1BACKUP DATABASE AdventureWorks2012  
 2TO TAPE = '.tape0', TAPE = '.tape1'  
 3MIRROR TO TAPE = '.tape2', TAPE = '.tape3'  
 4WITH  
 5   FORMAT,  
 6   MEDIANAME = 'AdventureWorksSet1'; 
 7
 8
 9BACKUP DATABASE AdventureWorks2012 TO TAPE  ='.tape0'  
10BACKUP LOG AdventureWorks2012 TO TAPE ='.tape0'
Analysis Results
  Message Line Column
1 SA0200 : Backup to tape syntax is deprecated. 2 3
2 SA0200 : Backup to tape syntax is deprecated. 2 23
3 SA0200 : Backup to tape syntax is deprecated. 9 38
4 SA0200 : Backup to tape syntax is deprecated. 10 33
See Also

Other Resources

Analysis Rules