Wednesday, October 1, 2014

0 Restore database fail in SQL2008R2

This is very old bug but I just encounter this myself  recently. 

While try to restore the database from the backup, the restore fail. When I look closely with the error log, I notice that the database logical name has been truncate ….

Here are the steps how to reproduce the issue.

Create database testDB;
GO
USE [testDB]
GO
ALTER DATABASE [testDB] MODIFY FILE (NAME=N'testDB', NEWNAME=N'testDB_TEST')
GO
backup database [testDB] to disk='C:\TEMP\testDB.bak' with init, stats=10
GO
RESTORE FILELISTONLY from disk = 'C:\TEMP\testDB.bak'
GO

image

The backup file show the logical database file name become ‘testDB_TES’, however from sys.database_file, it should be ‘testDB_TEST’

image

If we run the restore, we will get the error message like below

RESTORE DATABASE [testDB] from disk='C:\TEMP\testDB.bak' with replace;

Msg 3102, Level 16, State 1, Line 11
RESTORE cannot process database 'testDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally
.

image
We have to restore the backup using WITH MOVE.  The other alternative is to bounce SQL Server and after we execute the alter database command , then take the backup afterword. But I think if you do need to restore the DB, this option probably is not going to work for you.

use master
go
RESTORE DATABASE  [testDB] from disk='C:\TEMP\testDB.bak'
with replace, 
move 'testDB_TES' to 'C:\DISK\SGC2\DB\DATA\testDB.mdf',
move 'testDB_log' to 'C:\DISK\SGC2\DB\DATA\testDB_log.LDF';

This is one of the earlier bug in SQL 2008R2 https://support2.microsoft.com/kb/2487231 . The ultimate the fix is to upgrade to latest SP.

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates