?
Solved

restore a database and I get error

Posted on 2013-01-03
5
Medium Priority
?
407 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 25

Expert Comment

by:DBAduck - Ben Miller
ID: 38742686
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
ID: 38742689
Can you explain me with transact sql code Please
0
 
LVL 8

Assisted Solution

by:Crashman
Crashman earned 668 total points
ID: 38742696
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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 664 total points
ID: 38742699
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 668 total points
ID: 38742851
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

840 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