Solved

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

Posted on 2011-03-11
4
949 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

828 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