CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Himanshu
Himanshu

Reputation: 17

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

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

Upvotes: 0

Views: 117

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

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

Upvotes: 1

Related Questions