Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1029
  • Last Modified:

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

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
kcmoore
Asked:
kcmoore
  • 3
1 Solution
 
dba2dbaCommented:
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
 
kcmooreAuthor Commented:
I do have the WITH REPLACE option in my original script and still received the error.
0
 
kcmooreAuthor Commented:
I was able to complete this through the UI in SQL by changing the file locations.
0
 
kcmooreAuthor Commented:
solved this issue myself
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now