We help IT Professionals succeed at work.

Trying to restore .mdf file in SQL Server 2005 to different name than original database name

alexisbr
alexisbr asked
on
931 Views
Last Modified: 2012-06-21
Hi.  I have been checking posts and trying different solutions but can't seem to figure this one out.   I have a backup of a sql server 2005 databases in mdf file format.  I do not have an ldf file.  I want to restore this mdf file to a different database name so I can review it and extract some deleted data that no longer exists in the live database.  But when I try to attach the file name, even though I have renamed the mdf file to the new name I want to use, the restore process sees the original database name and gives me an error "Cannot attach a database with the same name as an existing database".  Is there a way I can restore this mdf file originally created from database name mydb to new database name mydb052412?

Thanks,
Alexis
Comment
Watch Question

CERTIFIED EXPERT

Commented:
If it is an mdf you should be attaching not restoring? (IE and attach is the method for mdf files).

Pehaps you should backup the orginal database as a .bak file using the backup routines, then doing a Restore from the bak file.
This should allow you to restore as a newly named DB and the MDF and LDF files should automatically be named as per the new DB name which should be unique, hence not giving a "Cannot attach a database with the same name as an existing database" error.

Author

Commented:
Sorry I didn't explain myself very well.  I have a live database that had data deleted over the last few days.  This data exists in a backup done 4 days ago but the backup was done as an mdf file rather than a true SQL Server backup to a .bak file.  I do not want to replace the existing live database since it contains additional data since the mdf file backup from 4 days ago.  Therefore, I want to "restore" the mdf file that I have from 4 days ago into a different database name so I can select certain data from it and put it back in the live database.   I tried to attach the mdf file but the attach process is not working because it sees the existing live database and says I can't attach a database with the same name.  I want to attach the mdf file to a different database name.   Here's an example.  The live database name is mydb.  I have an mdf file called mydb.mdf that was created 4 days ago.  I want to attach it to a new database called mydb052412 but SQL server gives me an error during the attach process because it tells me that mydb already exists.  It is ignoring the fact that I want to call the new database mydb052412.  I renamed the mydb.mdf file to mydb052412.mdf but it still gives me the same error.  No matter what I do, the attach process still sees the mdf file as attaching to mydb, which is the same name as the current live database.   I hope this makes more sense.  I don't believe this client has a full development version of SQL Server 2005 so that may be part of the problem.  I will try to figure out exactly what version we are working with.

Thanks for your help.

Alexis
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
try this

   EXEC sp_attach_db @dbname = N'NEWDBNAME', 
   @filename1 = N'c:\data\dbfile.mdf',

Open in new window

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
or use ATTACH_REBUILD_LOG

CREATE DATABASE [NEWDBNAME] ON 
	(filename = N'c:\data\dbfile.mdf')
	FOR ATTACH_REBUILD_LOG

Open in new window

Author

Commented:
Thanks!  I had already tried the sp_attach_db statement last night but it didn't work.  I will try the attach-rebuild_log later.  Unfortunately, the server is down and the client is trying to find out what happened.  

I was thinking about another way to possibly get around this.  What if I renamed the live database temporarily and then did the sp_attach_db statement?  Then that should work and, after attaching the older version, I can rename it and then rename the live db back to its correct name?  I can't try this again until later tonight but I will post back the results of your suggestions.

Thanks again.
Alexis
CERTIFIED EXPERT

Commented:
Try it, the problem you may encounter here will be the live database doesn't change the name of the mdf or ldf files when you change the DB name. We could be back at square 1 ie "Cannot attach a database with the same name as an existing database".

Author

Commented:
I tried ewangoya's attach_rebuild_log statement above but got the error below:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\hotline_log.ldf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\hotline_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'hotline052412'. CREATE DATABASE is aborted.

Open in new window


There is an existing log file in the folder but it goes with the current live db.  Is there a way for me to tell it to ignore the existing log file?  I do have a copy now of the ldf from 5/24/12 that goes with the mdf from 5/24/12 o can I point the mdf to the ldf and try to attach that way?  

Thanks,
Alexis

Author

Commented:
Tony303,
I thought about what you said on attaching to a different server.  I was able to use a different server and the attach db worked!  Now I can get the data I need.

Thanks to everyone for your help.

Alexis

Author

Commented:
Thank you!
CERTIFIED EXPERT

Commented:
Stressful times, I know what it is like with a customer breathing down your neck, especially when the problem remains for a few days. (IE. no sleep or at best disturbed sleep).

go well.

Tony
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.