Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cannot restore deleted Database in SQL Server 2008

Posted on 2011-05-06
9
Medium Priority
?
752 Views
Last Modified: 2012-05-11
Good day everyone,

I have logshipping enabled for several databases, it works fine in them all but there is a couple which are giving me this error when I try to restore them:

System.Data.SqlClient.SqlError: File 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DATABASENAME.mdf' is claimed by 'DATABASENAME_log'(2) and 'DATABASENAME'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

I had restored them before for testing and it worked, but some weeks after when I wanted to enable logshipping for real using fresh back ups to restore I am getting this all the time. I even removed the database but still having the same issue, no way I can restore it.
0
Comment
Question by:jimbobrocks
  • 4
  • 4
9 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35708686
When you use Management Studio for the restore Fill in the information in the Restore window. Before you click OK select the Options section on the left. There you can supply different database files. This way you should be able to do the restore.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35711186
IS there a file exist in location? Yes, That's why giving the error.
0
 

Author Comment

by:jimbobrocks
ID: 35721178
No the database file which complains about doesn't exist at all.

I will try to change the name of the files to restore, although I don't like the solution, but it may do the trick.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35723057
Could this be your issue:
http://social.msdn.microsoft.com/Forums/en-NZ/sqltools/thread/b14d9b3f-3f66-4d09-842e-e83a3a31562f
This user had multiple database files, and SQL server tried to restore them with the same name.
0
 

Author Comment

by:jimbobrocks
ID: 35723122
It doesn't matter what filename I try to restore the database into it just fails with the same error. Maybe there is something wrong in the back up?

Regards.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35723164
There are two rows in the database files list right? Do you perhaps specify the same filename on both rows?
0
 

Author Comment

by:jimbobrocks
ID: 35724428
Almost what you said!

The log and the row had the same file name in the back up! Just changed them so it make sense and now it restores properly.
0
 

Author Comment

by:jimbobrocks
ID: 35724467
In one of the databases i have like 15 rows of data with the same name for restore, is there a way they can get the name in a smart way rather than editing them by hand?

Regards
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35726533
You could use the restore database command with the move clause. The command would look something like this:
RESTORE DATABASE dbname FROM DISK = N'c:\backup\dbname.bak'
WITH MOVE dbname to 'c:\data\dbname.mdf'
MOVE dbname_log to 'c:\data\dbname.ldf'

Open in new window

But with 15 move clauses in your situation.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

810 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