Solved

restore a database and I get error

Posted on 2013-01-03
5
396 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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