BUG: DCR: Enterprise Manager May Allow You to Select Backups that No Longer Exist (275696)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q275696
BUG #: 58487 (SQLBUG_70)
BUG #: 236560 (SHILOH)

SYMPTOMS

SQL Server Enterprise Manager may allow you to select and restore backups that no longer exist. Enterprise Manager may then either restore the latest backup or an error may occur while restoring.

CAUSE

Enterprise Manager reads the backupset and backupmediafamily tables from the msdb database to populate the restore database screen with the list of backups. These tables store a history of backups. The tables are not updated when a backup is overwritten with a BACKUP WITH INIT statement.

WORKAROUND

Use RESTORE HEADERONLY to verify the contents of the backup device.

A sample RESTORE HEADERONLY statement and output follows: 
RESTORE HEADERONLY FROM DISK='D:\pubstrial.bak'
A subset of the output is: 
BackupName            UserName ServerName DatabaseName BackupStartDate          BackupFinishDate                                                                                                       
--------------------- -------- ---------- ------------ ------------------------ ------------------------ 
pubstrial             sa       TEST22     pubs         2000-12-08 14:21:36.000  2000-12-08 14:21:38.000 
pubs backup           sa       TEST22     pubs         2000-12-08 14:21:47.000  2000-12-08 14:21:48.000


You can look at the BackupStartDate and BackupFinishDate columns to determine the correct backup to restore.

If no other backups are taken after you perform a BACKUP WITH INIT, then the Restore Headeronly statement should return only one record.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0 and SQL Server 2000.

MORE INFORMATION

Steps To Reproduce Behavior

  1. Create a backup of the pubs database to a file. For example, C:\Mssql7\Backup\Pubs1.
  2. Create a new table, named test1, in the pubs database. The test1 table is not present in the backup taken earlier.
  3. In the SQL Server Query Analyzer run this statement:
    BACKUP database pubs to disk ='C:\mssql7\backup\pubs1' with INIT
  4. Run RESTORE HEADERONLY for that device. The output shows one record with the most recent backup, because the INIT overwrites the earlier backup on that device. You can check the BackupStartDate field and note that it matches the time of the BACKUP WITH INIT backup that was just taken.
  5. Navigate to the SQL Server Enterprise Manager. Right-click pubs, and then click Choose All Tasks / Restore Database.
  6. In the Restore database dialog box, the display shows both the old and the new backup in First Backup To Restore drop-down list box.
  7. Select the older backup by selecting the corresponding check box in the control that is underneath the Point in time restore check box, and then click OK. The database is restored and a message that states the restore was successful appears.
  8. If you navigate to the pubs database and refresh that database, SQL Server Enterprise Manager still shows you the table test1 that you created after taking the first backup. This implies that the newer has restored the newer backup from the device.
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbBug kbpending KB275696
©2003 Microsoft Corporation. All rights reserved.