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

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.

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

try this

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

Select allOpen in new window

or use ATTACH_REBUILD_LOG

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

Select allOpen in new window

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

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".

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.

Select allOpen 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

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

Thank you!

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