I am trying to restore db with single bak file which have multiple db abcdb,abcdb_index,abcdb_max,abcdb_log

0
votes

I am getting the error can u pls help

RESTORE DATABASE [abcdb] 
FROM DISK = N'C:\Shared\Shared\AerdemDB\abcdb_20200225000000\abcdb_20200225000000.bak' 
WITH FILE = 1, 
MOVE N'abcDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB.MDF', 
MOVE N'ABCDB_INDEX' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB.MDF', 
MOVE N'ABCDB_MAC' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB.MDF', 
MOVE N'SMUDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB_1.LDF', 
NOUNLOAD, 
STATS = 10

Restore failed for Server 'LI0605305'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB.MDF' is claimed by 'ABCDB_INDEX'(3) and 'ABCDB'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476)

My single bak file contains this has 3 mdf file with abcdb , abcdb_index, abcdb_mac and one log file abcdb_log

1
sqlsql-serversql-server-2008-r2
What is the statement you're using here? It seems like you should be using REPLACE, as the database already exists on your instance. – Larnu
Also, 2008(R2) has been unsupported for a year now; you should really be looking at upgrade paths as soon as possible, if not already. – Larnu
What statement? Edit your question. – Larnu
Edit your question, not the comment, and put the code you are using in a code fence. – Larnu
WEll, as i suspected, there's no REPLACE in your statement. As the database abcdb already exists you have to use REPLACE. – Larnu

1 Answers

1
votes

Each file has to be restored to a seperate location, eg

RESTORE DATABASE [abcdb] 
FROM DISK = N'C:\Shared\Shared\AerdemDB\abcdb_20200225000000\abcdb_20200225000000.bak' 
WITH FILE = 1, 
MOVE N'abcDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB.MDF', 
MOVE N'ABCDB_INDEX' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB_INDEX.NDF', 
MOVE N'ABCDB_MAC' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB_MAC.NDF', 
MOVE N'SMUDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ABCDB_1.LDF', 
NOUNLOAD, 
STATS = 10