Home Dashboard Directory Help
Search

SQL Server 2008R2 backup files have truncated logical file names by medcom


Status: 

Active


10
0
Sign in
to vote
Type: Bug
ID: 614730
Opened: 10/18/2010 12:51:08 PM
Access Restriction: Public
2
Workaround(s)
view
11
User(s) can reproduce this bug

Description


I am using this command to back up a database:

osql -S (local) -E -Q "backup database mydb to disk = 'mydb.bak' WITH STATS = 10"

I am then using this command to restore:

osql -S (local) -E -Q "RESTORE DATABASE mydb2 FROM DISK = 'mydb.bak' WITH MOVE 'mydb' TO 'c:\mydb2.mdf', MOVE 'mydb_Log' TO 'c:\mydb2_Log.ldf', REPLACE, STATS = 10"

I get the error "Logical file is not part of database". When I use "RESTORE FILELISTONLY", it shows the logical filenames are truncated by one character, i.e. "myd" and "mydb_Lo". This does not happen with all databases, and I have not been able to identify a pattern.

Details
Sign in to post a comment.
Posted by ADOConnection on 8/9/2011 at 1:34 PM
During install something went wrong. Now I see " TITLE: Connect to Server

Login failed for user '***\Administrator'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

:(
Posted by fuehrerm on 4/6/2011 at 10:19 PM
Yes, I have verified it is now working correctly after applying CU6 for our automated system.
Posted by Ben Seaman on 3/11/2011 at 10:42 AM
Looks like this is fixed in CU6 for SQL 2008 R2:

http://support.microsoft.com/kb/2487231
Posted by Cheeka 75 on 2/28/2011 at 10:31 AM
MS team,

Any updates on when the fix may be available?
Posted by Greg Faulk on 2/23/2011 at 1:52 PM
With regard to the comment posted by Microsoft on 12/6/2010 at 1:21 PM

Are you saying customers will have to wait for the "next major release" or will this be addressed in a CU? Would you consider 2008 R2 SP1 a major release or do we have to wait for SQL Server 11?
Posted by Zac Fenigshtien on 2/20/2011 at 3:08 AM
the problem dosn't occur in 2008 only in 2008 R2
Posted by Zac Fenigshtien on 2/20/2011 at 2:18 AM
The problem might be due to renaming of the logical file names of the db before backup. The logical names that appear in any backup after renaming are trunacted. Without renaimg, there isn't any problem.

Repro:

USE master
go

CREATE DATABASE XXX
go

--check logical file names : all fine
select db.name DatabaseName, files.name logical_filename, files.filename physical_filename from master.sys.databases db, master.sys.sysaltfiles files
where db.database_id = files.dbid AND db.name = 'XXX'
go

BACKUP DATABASE XXX
TO DISK = N'C:\XXX.bak' WITH init
go

--check logical file names in backup set: all fine
RESTORE FILELISTONLY FROM DISK = N'C:\XXX.bak'
go

-- alter the logical name
ALTER DATABASE XXX MODIFY FILE (NAME=N'XXX', NEWNAME=N'YYY')
go

--check logical file names : all fine, file name has been altered
select db.name DatabaseName, files.name logical_filename, files.filename physical_filename from master.sys.databases db, master.sys.sysaltfiles files
where db.database_id = files.dbid AND db.name = 'XXX'
go

-- back up to a new bu set
BACKUP DATABASE XXX
TO DISK = N'c:\YYY.bak' WITH init
go

--check logical file names in the new backup set: FILE NAME HAS BEEN TRUNCATED
RESTORE FILELISTONLY FROM DISK = N'C:\YYY.bak'
go
Posted by fuehrerm on 1/10/2011 at 1:18 PM
Update: the problem appears for me if the logical name contains numbers and under score characters.

IE:

data1
data2

Or

data_1
data_2
Posted by fuehrerm on 1/7/2011 at 2:56 PM
Do you know when the hotfix will be ready? We have duplicated this bug as well and need to get it resolved because it breaks our automated system.

Thanks
Posted by Thomas Briggs on 1/5/2011 at 9:05 AM
Is the fix for this included in SQL 2008R2 Cumulative Update 5? If not when will a fix be available?
Posted by Roger [MSFT] on 12/7/2010 at 12:16 PM
great thanks, hopefully also for 2008 :)
Roger
Posted by Microsoft on 12/6/2010 at 1:21 PM
Greetings.

This has been fixed, and we are in the process of producing a hot fix for SQL Server 2008 R2.

We have verified that this behavior does not occur in current builds of the next major release.
Posted by Roger [MSFT] on 12/3/2010 at 5:34 AM
Hi we can repro also with the following Script:

USE master
GO

CREATE DATABASE TestDB
GO

BACKUP DATABASE TestDB TO DISK='d:\TestDB_1.BAK'
GO
SP_HELPDB 'TestDB' -- filenames are correct!
GO

ALTER DATABASE [TestDB] ADD FILE ( NAME = N'TestDB0', FILENAME = N'D:\TestDB0.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
ALTER DATABASE TestDB MODIFY FILE (NAME=TestDB, NEWNAME=TestDB0u)
GO
SP_HELPDB 'TestDB' -- filenames are correct!
GO

BACKUP DATABASE TestDB TO DISK='d:\TestDB_2.BAK'
GO

RESTORE FILELISTONLY FROM DISK='d:\TestDB_2.BAK' -- delivers 2 identical logical filenames!

Collation= SQL_Latin1_General_CP1_CI_AS
Posted by medcom on 10/27/2010 at 2:09 PM
Here is a sample from that result:

MCSR_StaticData    MCSR_StaticData    D:\SQL2008\Data\MCSR_StaticData.mdf
MCSR_StaticData    MCSR_StaticData_log    D:\SQL2008\Data\MCSR_StaticData_log.ldf

In the backup, these logical names become "MCSR_StaticDat" and "MCSR_StaticData_lo"

These truncated names also show up in the Restore Database dialog. Also, as I said before, after restore the logical filenames are back to what they are supposed to be. So if someone just uses the UI they would never see the problem.
Posted by Microsoft on 10/27/2010 at 10:14 AM
Please try running the following query on both instances:

select db.name DatabaseName, files.name logical_filename, files.filename physical_filename from master.sys.databases db, master.sys.sysaltfiles files
where db.database_id = files.dbid
order by dbid

This should show the logical and physical names for all files for all databases in each instance. I need to see what the names are prior to backup, to see where the problem is.

Thanks,
Kevin
Posted by medcom on 10/21/2010 at 6:53 AM
It's odd that you haven't run across this; I have it on two servers (D1 and D2), both with 2008R2 Developer. D1 was installed fresh, D2 was upgraded from 2008. I am copying databases from D1 to D2, and backing up and restoring databases on D2. I have gotten the issue with three different databases now. Another odd thing: after the database is restored, it has the correct logical names. So if someone is only using the UI they won't notice the problem at all.

I didn't do anything funky with the installs, mostly the defaults.

Server Collation: SQL_Latin1_General_CP1_CI_AS
Language: English (United States)
Posted by Microsoft on 10/20/2010 at 3:31 PM
Greetings,
Thanks for your report.
It's an interesting situation, because our automated testing covers this case explicitly, and should have caught the problem.
What collation and locale are you using in your instance?

Kevin Farlee
Sign in to post a workaround.
Posted by retracement on 11/1/2011 at 4:51 AM
Actually Ben I've tried it and it does work. After a bit of testing I have found that it is possible to set the database offline and online -which overcomes the issue or even setting to read_only the read_write (the best alternative so far).
Posted by Ben Seaman on 3/11/2011 at 10:44 AM
According to the KB at http://support.microsoft.com/kb/2487231 which states that this issue is resolved in CU6 for SQL 2008 R2, you can get around the issue by restarting SQL after a logical database filename is changed.

disclaimer - i haven't tested this myself....