When you use SQL Server Enterprise Manager to backup and verify the backup of a database, the verification assessment of the backup is not reliable if the backup set is appended to either a previously existing file or device.
Furthermore, if you attempt to establish a Database Maintenance Plan through the Database Maintenance Wizard and you select Verify the Backup to a tape that contains a Microsoft Windows NT file backup that is File number 1, you will see this error message:
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 3143: [Microsoft][ODBC SQL Server Driver][SQL Server]
The data set on device '\\.\Tape0' is not a SQL Server backup set.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore
Also, if you put a SQL Server database backup on the tape first and you then attempt to perform a Windows NT backup, a Dr. Watson error (0xc000005) occurs.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
To reproduce this problem from within SQL Server Enterprise Manager, follow these steps:
Select a database (for example, pubs).
Right-click All Tasks...Backup Database.
Perform a backup to some file.
Leave the default Append to media selected.
In the Options dialog box, select Verify backup upon completion.
You will see a message that indicates the backup operation and its verification have completed successfully. However, the RESTORE VERIFYONLY option may show in Profiler that it always executes against FILE=1. However, if the backup task is appended to the device or file and then the backup is repeated it will not be the last backup set in that file.