?
Solved

Restore sql 2008 db with multiple ndf files to db with different name

Posted on 2011-03-11
4
Medium Priority
?
1,040 Views
Last Modified: 2012-05-11
I need to restore a sql 2008 db that has multiple ndf files to a differently named db instance.

When I run this script:


RESTORE DATABASE [UT2]
  FILE = N'MM_Data'
, FILE = N'MM_Data_Shrink'
, FILE = N'MM_NIndexSD'
, FILE = N'MM_NIndex'
, FILE = N'MM_Log'
FROM DISK = N'C:\DB\Backups\MM_Full.bak' WITH FILE = 1
, MOVE N'MM_Data' TO N'C:\DB\UT2.mdf'
, MOVE N'MM_Data_Shrink' TO N'C:\DB\UT2.Ndf'
, MOVE N'MM_NIndexSD' TO N'C:\DB\UT2_1.NDF'
, MOVE N'MM_NIndex' TO N'C:\DB\UT2_2.NDF'
, MOVE N'MM_Log' TO N'C:\DB\UT2.ldf'
, NORECOVERY, REPLACE, STATS = 10
GO


I get this error:

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'UT2' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


What setting in the script am I missing?
0
Comment
Question by:kcmoore
  • 3
4 Comments
 
LVL 8

Expert Comment

by:dba2dba
ID: 35110046
Please use WITH REPLACE option. It will be -

RESTORE DATABASE [UT2]
  FILE = N'MM_Data'
, FILE = N'MM_Data_Shrink'
, FILE = N'MM_NIndexSD'
, FILE = N'MM_NIndex'
, FILE = N'MM_Log'
FROM DISK = N'C:\DB\Backups\MM_Full.bak' WITH FILE = 1
, MOVE N'MM_Data' TO N'C:\DB\UT2.mdf'
, MOVE N'MM_Data_Shrink' TO N'C:\DB\UT2.Ndf'
, MOVE N'MM_NIndexSD' TO N'C:\DB\UT2_1.NDF'
, MOVE N'MM_NIndex' TO N'C:\DB\UT2_2.NDF'
, MOVE N'MM_Log' TO N'C:\DB\UT2.ldf'
, NORECOVERY, REPLACE, STATS = 10,REPLACE
0
 

Author Comment

by:kcmoore
ID: 35110074
I do have the WITH REPLACE option in my original script and still received the error.
0
 

Accepted Solution

by:
kcmoore earned 0 total points
ID: 35110843
I was able to complete this through the UI in SQL by changing the file locations.
0
 

Author Closing Comment

by:kcmoore
ID: 35145541
solved this issue myself
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
The Exchange database may sometimes fail to mount owing to various technical reasons. A dismounted EDB file can be the source of many Exchange errors including mailbox inaccessibility for users. Resolving the root cause of mounting problems becomes …
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question