SA0200 : Backup to tape syntax is deprecated
Introduction
Section titled “Introduction”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
Section titled “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:
-- Example of deprecated usageBACKUP 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
Section titled “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:
-- Example of corrected queryBACKUP DATABASE MyDatabase TO DISK = 'C:\Backup\MyDatabase.bak';The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”Deprecated Database Engine Features in SQL Server 2017
Example Test SQL
Section titled “Example Test SQL”BACKUP DATABASE AdventureWorks2012TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'WITH FORMAT, MEDIANAME = 'AdventureWorksSet1';
BACKUP DATABASE AdventureWorks2012 TO TAPE ='\\.\tape0'BACKUP LOG AdventureWorks2012 TO TAPE ='\\.\tape0'Analysis Results
Section titled “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 |