Microsoft KB Archive/817089

= BUG: Recovery of database logical file names might fail after a RESTORE operation =

Article ID: 817089

Article Last Modified on 4/9/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



BUG #: 356866 ( SQL Server 8.0)



SYMPTOMS
When you restore a database, recovery of logical file names of the database can fail, and the logical file names that correspond to the database in the master..sysaltfiles and the ..sysfiles tables may differ.



CAUSE
When you perform a backup and restore operation with a database that is in full recovery mode, this problem occurs if you modify the logical file names in the database after you back up the database but before you back up the transaction log.



Service pack information
To resolve this problem, obtain the latest service pack for SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 How to obtain the latest SQL Server 2000 service pack



WORKAROUND
If you are already experiencing this problem, follow these steps to work around the problem:
 * 1) Change the logical file names to names that are different from the names that you want to use.
 * 2) Rename the logical file names to the names that you want.

Note If you directly rename the logical files to the names that you want, the rename operation might fail because the new names already exist and are updated in one of the system tables. Therefore, you must rename the logical files to some other name, and then rename them to the names that you want. Make sure that you take the database and log backups after renaming.

To avoid this problem when you want to modify logical file names, follow these steps (in the same sequence):   Rename a logical file name in the database. For example: ALTER DATABASE  MODIFY FILE (NAME =  NEWNAME = )

GO   Back up the database and the transaction log files. For example: BACKUP DATABASE  TO DISK= ''

GO

BACKUP LOG  TO DISK= '<Backup file with path>'

GO </li>  Restore the database and the log files. For example: RESTORE DATABASE <Database name> FROM DISK = '<Disk drive>' WITH FILE = <File number>, NORECOVERY

GO

RESTORE LOG FROM DISK = ' ' WITH FILE =, RECOVERY

GO </li></ol>

Note Verify the master..sysaltfiles and <Database_name>..sysfiles to make sure that there is no mismatch.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section. This problem was first corrected in SQL Server 2000 Service Pack 4.

<div class="moreinformation_section">

MORE INFORMATION
The logical file names in master..sysaltfiles and  ..sysfiles must be the same. To synchronize the logical file names in both the tables, perform modifications to the logical file names before you back up the database or the transaction log.

You can identify the files that you must recover from each database backup set by using a RESTORE FILELISTONLY command. Use the RESTORE command to recover the database and the log files from the appropriate backup sets by using the WITH FILE and the MOVE options.

Steps to reproduce the behavior
Note Run the following Transact-SQL statements in the SQL Query Analyzer. <ol>  Create a new database and name it test. For example: CREATE DATABASE test

ON

PRIMARY

(NAME = 'test_data_1', FILENAME = 'c:\test_data_1.dat' , SIZE = 2)

(NAME = 'test_data_2', FILENAME = 'c:\test_data_2.dat' , SIZE = 2)

LOG ON

(NAME = 'test_log_1', FILENAME = 'c:\test_log_1.dat' , SIZE = 2)

(NAME = 'test_log_2', FILENAME = 'c:\test_log_2.dat' , SIZE = 2)

GO </li>  Set the recovery mode of test to full recovery. For example: ALTER DATABASE test SET RECOVERY FULL

GO </li>  Back up test to a disk device. For example: BACKUP DATABASE test TO DISK = 'C:\Test.dmp' WITH FORMAT

GO </li>  Change the logical file name of several files in test: ALTER DATABASE test MODIFY FILE

(NAME = 'test_data_1', NEWNAME = ' test_data_1_new')

ALTER DATABASE test MODIFY FILE

(NAME = 'test_log_2', NEWNAME = ' test_log_2_new')

GO </li>  Back up the log file for test: BACKUP LOG test TO DISK = 'c:\test.dmp'

GO </li>  Restore test and the log files that correspond to test: RESTORE DATABASE test FROM DISK = 'c:\test.dmp' WITH FILE = 1, NORECOVERY

RESTORE LOG test FROM DISK = 'c:\test.dmp' WITH FILE = 2, RECOVERY

GO </li>  Compare the contents of the test..sysfiles table and the contents of the master..sysaltfiles table that corresponds to test: SELECT * FROM test..sysfiles

SELECT * FROM master..sysaltfiles WHERE dbid= DB_ID('test')

GO Note You can see different entries for the logical file names in the tables. </li></ol>

<div class="references_section">