restore a database and I get error

Hi experts, I am trying to restore a database and I get error, please see attached file
Error-Restaurar-BaseDat.png
enrique_aeoAsked:
Who is Participating?
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
This is telling you that you are trying to restore a database and the file(s) are already in use by another database.  So you need to use the MOVE option to move them to another filename.
0
 
enrique_aeoAuthor Commented:
Can you explain me with transact sql code Please
0
 
CrashmanConnect With a Mentor Commented:
Take a look

http://sqlgiant.wordpress.com/tag/the-with-move-clause-can-be-used-to-relocate-one-or-more-files/


Strangely enough I read the error message a few times, and for once I was able to determine what the problem was.  Here was the key to the error message:  File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ SomeNewDB.mdf’ is claimed by ‘SomeNewDB_UserData’(3) and ‘SomeNewDB_Primary’(1).

So what I did was script out the restore and discovered that I was trying to use the .mdf file twice.

RESTORE DATABASE [SomeNewDB]

FROM DISK = N’C:\SQLBackup\SomeNewDB.bak’

WITH FILE = 1,

MOVE N’SomeNewDB_Primary’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_UserData’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_Log’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB_1.ldf’,

NOUNLOAD, REPLACE, STATS = 10

GO

 

So I changed the second file to .ndf and ran the script and it seemed to work.

RESTORE DATABASE [SomeNewDB]

FROM DISK = N’C:\SQLBackup\SomeNewDB.bak’

WITH FILE = 1,

MOVE N’SomeNewDB_Primary’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_UserData’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.ndf’,

MOVE N’SomeNewDB_Log’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB_1.ldf’,

NOUNLOAD, REPLACE, STATS = 10

GO

So if you ever run into that problem make sure your not trying to reference the .mdf file twice inside your restore statement.

also this

http://serverfault.com/questions/229952/error-restoring-database-from-backup
0
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
Well, if you click the script button, you will see something like

RESTORE DATABASE databasename FROM DISK='c:\database.bak' WITH REPLACE

What the message is telling you is to use this type of command.

RESTORE DATABASE [ANDY_AYLAS-PC]
FROM DISK='c:\database.bak'
WITH MOVE 'TRAMITE_Datos' TO 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\DATA\STD_INABIF_ANDY.mdf',
MOVE 'TRAMITE_USER' TO 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\DATA\STD_INABIF_ANDY_2.ndf',
MOVE 'TRAMITE_Registro' TO 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\DATA\STD_INABIF_ANDY_Log.ldf',
REPLACE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.