BUG: Recovery of Database Logical File Names Might Fail After a RESTORE Operation (817089)

WORKAROUND

If you are already experiencing this problem, follow these steps to work around the problem:

Change the logical file names to names that are different from the names that you want to use.
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 <DATABASENAME> MODIFY FILE  (NAME = <Old file name> NEWNAME = <New file name>)
GO

Back up the database and the transaction log files. For example:

BACKUP DATABASE <Database name> TO DISK= '<Backup file with path>' 
GO
BACKUP LOG <Database name> TO DISK=  '<Backup file with path>'
GO

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 <database name> FROM DISK = '<disk drive>'  WITH FILE = <file number>, RECOVERY
GO

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

MORE INFORMATION

The logical file names in master..sysaltfiles and <database name>..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.

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

Set the recovery mode of test to full recovery. For example:
```
ALTER DATABASE test SET RECOVERY FULL
GO
```

Back up test to a disk device. For example:

BACKUP DATABASE test TO DISK = 'C:\Test.dmp' WITH FORMAT
GO

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

Back up the log file for test:

BACKUP LOG test TO DISK = 'c:\test.dmp'
GO

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

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.

BUG: Recovery of Database Logical File Names Might Fail After a RESTORE Operation (817089)

SYMPTOMS

CAUSE

WORKAROUND

STATUS

MORE INFORMATION

Steps to Reproduce the Behavior

REFERENCES