Solved

restore a database and I get error

Posted on 2013-01-03
5
393 Views
Last Modified: 2013-01-10
Hi experts, I am trying to restore a database and I get error, please see attached file
Error-Restaurar-BaseDat.png
0
Comment
Question by:enrique_aeo
5 Comments
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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
 

Author Comment

by:enrique_aeo
Comment Utility
Can you explain me with transact sql code Please
0
 
LVL 8

Assisted Solution

by:Crashman
Crashman earned 167 total points
Comment Utility
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
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 166 total points
Comment Utility
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
 
LVL 14

Accepted Solution

by:
Jagdish Devaku earned 167 total points
Comment Utility
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now