Solved

restore a database and I get error

Posted on 2013-01-03
5
394 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
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 167 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 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 166 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 167 total points
ID: 38742851
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 31
SQL Help - 12 60
SQL Server 2008 R2 - Updating Table/Fields Documentation 3 68
SQL Transaction logs 8 27
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 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

22 Experts available now in Live!

Get 1:1 Help Now