Wednesday, October 1, 2014

1 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.

1 comments:

  1. This is very amazing for everyone that the problem of big data which the common man assume that it cannot be solve by the any person, amazingly these type of data problems are easily solve by the data scientist who are made for mange the big data for the people, and you can also get these data scientist form this seoanalytics.pro.

    ReplyDelete

 

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