June 30, 2015 at 8:12 pm
I am trying to restore a database to a different server from where the backup was created using the following script:
RESTORE FILELISTONLY
FROM DISK = 'C:\Extract\REZ_db_201506240733.BAK'
RESTORE DATABASE [REZ]
file = 'DB_1',
file = 'REZ_bio',
file = 'REZ_gift',
file = 'REZ_index',
file = 'REZ_temp'
FROM DISK = N'C:\Extract\REZ_db_201506240733.BAK' WITH FILE = 1,
MOVE N'DB_1' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'REZ_bio' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'REZ_gift' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'REZ_index' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'REZ_temp' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'DB_1_log' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ_mst_1.ldf',
NOUNLOAD, STATS = 10
GO
and I am getting the following errors:
(6 row(s) affected)
Msg 3176, Level 16, State 1, Line 4
File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_bio'(3) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3176, Level 16, State 1, Line 4
File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_gift'(4) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3176, Level 16, State 1, Line 4
File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_index'(5) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3176, Level 16, State 1, Line 4
File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_temp'(6) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
Although I have done this successfully a couple of other times before, I am not very knowledgeable about the restore process.
Can someone help me figure out what I am doing wrong?
I appreciate any help I can get.
Thanks,
Sup
July 1, 2015 at 1:24 am
Can you post the output of this please
RESTORE FILELISTONLY
FROM DISK = 'C:\Extract\REZ_db_201506240733.BAK'
The problem you have is the fact you're specifying the same file multiple times in the MOVE clause
RESTORE DATABASE [REZ]
file = 'DB_1',
file = 'REZ_bio',
file = 'REZ_gift',
file = 'REZ_index',
file = 'REZ_temp'
FROM DISK = N'C:\Extract\REZ_db_201506240733.BAK' WITH FILE = 1,
MOVE N'DB_1' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'REZ_bio' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\ REZ.mdf'
MOVE N'REZ_gift' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'REZ_index' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'REZ_temp' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',
MOVE N'DB_1_log' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ_mst_1.ldf',
NOUNLOAD, STATS = 10
GO
The error detail here tells you what the problem is
File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_temp'(6) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 1, 2015 at 2:29 am
I corrected the file names on the TO side of the move clauses and that did it!
I appreciate your help very much Perry!
Thanks,
Sup
July 1, 2015 at 7:54 am
you're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply