September 4, 2010 at 8:38 pm
I encountered a situation on a database that I've never seen before.
My database name is
Name1
Logical Names are
Data = Name1_Data
Log = Name1_Log
When I backup the file and check the logical names, it shows up as
Data = Name1_Dat
Log = Name1_Lo
I am then required to restore the database with a move using the new logical name instead of what the database info says:
For example:
restore database name1_test
from disk = 'backup file'
with
move 'Name1_Dat' to 'new data',
move 'Name1_Lo' to 'new_log file'
When I deatach the database and reattached and do a backup, the logical file names are correct again when I do a restore filelistonly, but the restores will no longer work. I actually have to change the logical names again to somehow make the system truncate the last letter. Now backups and restores are working again.
Anybody seen this?
September 6, 2010 at 12:38 am
Hay Steve,
It is sounding very strange!!! Is this issue is with only database or is it happening to all databases across the instance? BTW, which version of SQL server is this?
I am suspecting corrupt database engine or some virus activity
- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂
September 7, 2010 at 4:11 am
Hi
Can you provide the output of:
select name from Name1.sys.database_files
GO
restore filelistonly from disk='<your path to your full backup>'
GO
thx,
David B.
September 7, 2010 at 12:23 pm
COOL_ICE (9/6/2010)
Hay Steve,It is sounding very strange!!! Is this issue is with only database or is it happening to all databases across the instance? BTW, which version of SQL server is this?
I am suspecting corrupt database engine or some virus activity
This is only happening in one database. I have 10 other databases and I've checked and double checked the back files and they are OK.
version...
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
David BAFFALEUF (9/7/2010)
HiCan you provide the output of:
select name from Name1.sys.database_files
GO
restore filelistonly from disk='<your path to your full backup>'
GO
thx,
Out of sys database files. ( note, this is correct and what I expect to see in the backup files)
CDLA_Data
CDLA_Log
Backup Files
CDLA_DatF:\SqlData\CarpeDiemLA_Data.MDF
CDLA_LoG:\SqlLog\CarpeDiemLA_Log.LDF
-----
September 7, 2010 at 3:10 pm
OK. More testing done today and this is what I encountered.
1. Created a new database called "Test" with all defaults.
2. Backup database to file called "test.bak".
3. Check logical file name in the backup using RESTORE FILELISTONLY and the logical names are
Test
Test_log
4. Use alter database statement on database to append "Test" to the logical name.
Test > TestTest
Test_Log > Test_Logtest
5. Backup databse to file called "test2.bak"
6. Check logical file name in the backup using RESTORE FILELISTONLY and the logical names are
TestTes
Test_logTes
I've duplicated this on 2 Sql2008 R2 systems.
Continue...
7. Deleted my "Test" database.
8. Restored my test database using the Test2.bak files from Step 5.
RESTORE DATABASE Test2
FROM DISK = 'H:\SqlBackup\Test2.bak'
WITH
MOVE 'TestTest' TO 'F:\SqlData\Test2.MDF',
MOVE 'Test_logTesT' TO 'F:\SqlData\Test2.LDF'
Msg 3234, Level 16, State 2, Line 1
Logical file 'TestTest' is not part of database 'Test2'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
9. If i run the following, I'm OK.
RESTORE DATABASE Test2
FROM DISK = 'H:\SqlBackup\Test2.bak'
WITH
MOVE 'TestTes' TO 'F:\SqlData\Test2.MDF',
MOVE 'Test_logTes' TO 'F:\SqlData\Test2.LDF'
Processed 160 pages for database 'Test2', file 'TestTes' on file 1.
Processed 1 pages for database 'Test2', file 'Test_logTes' on file 1.
RESTORE DATABASE successfully processed 161 pages in 0.160 seconds (7.861 MB/sec).
See the differences in the logical files names?
10. Interestingly, if the database already exist and I do a restore with replace, I get the following:
RESTORE DATABASE Test2
FROM DISK = 'H:\SqlBackup\Test2.bak'
WITH
MOVE 'TestTest' TO 'F:\SqlData\Test2.MDF',
MOVE 'Test_logTest' TO 'F:\SqlData\Test2.LDF',
REPLACE
The file "TestTes" is selected. At the time of backup it was known by the name "TestTest". RESTORE will continue operating upon the renamed file.
The file "Test_logTes" is selected. At the time of backup it was known by the name "Test_logTest". RESTORE will continue operating upon the renamed file.
Processed 160 pages for database 'Test2', file 'TestTes' on file 1.
Processed 1 pages for database 'Test2', file 'Test_logTes' on file 1.
RESTORE DATABASE successfully processed 161 pages in 0.051 seconds (24.662 MB/sec).
11. If i use the truncate logical name, it will restore without any additional warnings.
RESTORE DATABASE Test2
FROM DISK = 'H:\SqlBackup\Test2.bak'
WITH
MOVE 'TestTes' TO 'F:\SqlData\Test2.MDF',
MOVE 'Test_logTes' TO 'F:\SqlData\Test2.LDF',
REPLACE
12. My teammate tried this on Sql 2008 (not sure about sp1 or not) and it did not exhibit this behavior. This looks to be a R2 only issue.
September 11, 2010 at 2:46 pm
Just an update:
A ticket was open with Microsoft support and they confirmed that they can reproduce this issue and are having the developers look into this issue.
I will update this as Microsoft updates our case notes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply