"RESTORE DATABASE is terminating abnormally" error message when you try to restore a full backup of a database taken in SQL Server 2008 R2 | SansSQL

Sunday, April 14, 2013

"RESTORE DATABASE is terminating abnormally" error message when you try to restore a full backup of a database taken in SQL Server 2008 R2

Consider a scenario in which you have a
  1. Created a database in SQL Server 2008 R2
    CREATE DATABASE [TestRestore]
    
  2. Changed the Logical Names of the database files
    USE master
    GO
    ALTER DATABASE TestRestore 
     MODIFY FILE ( NAME = 'TestRestore', NEWNAME = 'TestRestore_Data')
    GO
    ALTER DATABASE TestRestore 
     MODIFY FILE ( NAME = 'TestRestore_log', NEWNAME = 'TestRestore_logFile')
    
  3. Check the Logical File names
    SELECT * FROM sys.master_files WHERE DB_NAME(database_id)='TestRestore'
    
  4. Perform the full back of the databases
    BACKUP DATABASE TestRestore TO DISK = 'D:\Backup\TestRestore_FullBackup.bak' WITH STATS = 10, INIT
    
  5. Now try to restore the database using the full backup taken in Step 4
    RESTORE DATABASE [TestRestore_Restored] FROM  DISK = N'D:\Backup\TestRestore_FullBackup.bak' 
    WITH  FILE = 1,  
    MOVE N'TestRestore_Data' TO N'D:\Databases\TestRestore_Restored.mdf',  
    MOVE N'TestRestore_logFile' TO N'D:\Databases\TestRestore_Restored_1.ldf',  
    NOUNLOAD,  STATS = 10
    GO
    And you get the error
    Msg 3234, Level 16, State 2, Line 1 
    Logical file 'TestRestore_Data' is not part of database 'TestRestore_Restored'. Use RESTORE FILELISTONLY to list the logical file names. 
    Msg 3013, Level 16, State 1, Line 1 
    RESTORE DATABASE is terminating abnormally.
This is a known issue and is because, the logical name of the database, after the update is corrupted in the backup file. If you run the  RESTORE FILELISTONLY on the backup file, you will notice that the last character of the logical file name is truncated.
RESTORE FILELISTONLY FROM  DISK = N'D:\Backup\TestRestore_FullBackup.bak' 
Workaround
To work around this issue, use either one of the below method
  • After the logical file name is modified, take the database offline and then back to online.
    ALTER DATABASE TestRestore SET OFFLINE
    GO
    ALTER DATABASE TestRestore SET ONLINE
    GO
  • While modifying the logical name, append a white space at the end of the new file name, for example
    USE master
    GO
    ALTER DATABASE TestRestore 
     MODIFY FILE ( NAME = 'TestRestore', NEWNAME = 'TestRestore_Data ')
    GO
    ALTER DATABASE TestRestore 
     MODIFY FILE ( NAME = 'TestRestore_log', NEWNAME = 'TestRestore_logFile ')
    
Resolution
The fix for this issue was release in Cumulative Update 6 for SQL Server 2008 R2.
To resolve this issue permanently, apply the Cumulative Update 6 or the most recent update for SQL Server 2008 R2

No comments:

Post a Comment