Microsoft KB Archive/275696

= BUG: DCR: Enterprise Manager May Allow You to Select Backups that No Longer Exist =

Article ID: 275696

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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.



Steps To Reproduce Behavior
 Create a backup of the pubs database to a file. For example, C:\Mssql7\Backup\Pubs1. Create a new table, named test1, in the pubs database. The test1 table is not present in the backup taken earlier.  In the SQL Server Query Analyzer run this statement: BACKUP database pubs to disk ='C:\mssql7\backup\pubs1' with INIT  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.</li> Navigate to the SQL Server Enterprise Manager. Right-click pubs, and then click Choose All Tasks / Restore Database.</li> 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.</li> 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.</li> 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.</li></ol>

Keywords: kbbug kbpending KB275696

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.