Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now