Solved

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

Posted on 2011-03-11
4
981 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When speed and performance are vital to revenue, companies must have complete confidence in their cloud environment.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
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.

632 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