randypaulo 5:30 pm on September 13, 2011
Tags: Bugs,   

SQL Server 2008 R2 Logical name truncation during backup


We’re currently upgrading  an existing solution to use SQL Server 2008R2 from SQLServer 2005, the solution autogenerates database, performs couple of operations afterwhich it will create a backup.

During the restoration process that’s where we encountered the error:

Logical file ‘’ is not part of database ‘’. Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally.

So we take a peek on the generated backup file and discovers that the last letter of logical filename is being truncated. It happens when the logical name of the database is renamed before creating a backup.

You can try it using this script: (it works on SQL Server 2008R2 without any updates):

 USE [master]
GO

CREATE DATABASE [Test]
GO
ALTER DATABASE [Test] SET RECOVERY SIMPLE
GO

— Rename Logical Files and Start Backup
ALTER DATABASE [Test]
MODIFY FILE
(NAME = [Test], NEWNAME=’Test_New’)
GO

ALTER DATABASE [Test]
MODIFY FILE
(NAME = Test_log, NEWNAME=’Test_New_Log’)
GO

— Backup Working Copy
BACKUP DATABASE [Test] TO  DISK = N’E:\Test.bak ‘ WITH NOFORMAT, NOINIT,  NAME = N’Test_New’, SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

Message:

The file name ‘Test_New’ has been set.
The file name ‘Test_New_Log’ has been set.
13 percent processed.
22 percent processed.
31 percent processed.
40 percent processed.
53 percent processed.
62 percent processed.
71 percent processed.
80 percent processed.
94 percent processed.
Processed 176 pages for database ‘Test’, file ‘Test_Ne’ on file 2.
100 percent processed.
Processed 2 pages for database ‘Test’, file ‘Test_New_Lo’ on file 2.
BACKUP DATABASE successfully processed 178 pages in 0.027 seconds (51.486 MB/sec).

 Question is how did the QA guys missed this part,  it was working prior to SQL Server 2008R2.

A fix is released to resolve this issue.

Or with workaround:

To work around this issue, use one of the following two methods:

  • After the logical file name is modified, run the following statements to restart the database: 
    ALTER DATABASE  SET OFFLINE
ALTER DATABASE  SET ONLINE
  • When you modify the logical file name, append a dummy space character in the new logical name. For example, if you want to change the logical name to DB_modified, use the following statement to modify the logical file name: 
    ALTER DATABASE  MODIFY FILE
 (NAME=N'', NEWNAME='DB_modified ') -- Append ' ' to NEWNAME