Obscure Problems and Gotchas

This was a fun one, I couldn’t find a definitive answer on this so here is mine.

Question
How can I attach a database (MDF/LDF) to my server that has the exact same name as one of the databases in my server already?

Answer

1. Come up with a different name for the database that is to be attached (obviously)
   • This sounds obvious, but it matters. Come up with a decent name for your database.
   • If you are trying to attach an MDF and LDF that exist already then you need to rename the MDF and LDF files as well.
2. Find a safe place where the MDF and LDF are going to live permanently
   • This is important because while the server is running the MDF and LDF cannot be moved
   • If you stop the server and move the files later your server is going to complain
   • You can place the MDF and LDF where SQL Server stores everything by default. It looks like this: C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLDATA
     Just make sure that the files are named uniquely.
3. Modify the script below to your liking

USE [master]
GO
CREATE DATABASE [Desired_Unique_Name_Goes_Here] ON 
( FILENAME = N'C:...DataBaseToAttach.mdf' ),
( FILENAME = N'C:...DataBaseToAttach_log.ldf' )
 FOR ATTACH
GO

You can use the GUI to do this too, but the script is just as fast if not faster to work with if you are doing this repeatedly. I generated this script from the GUI just as an FYI.

Some Warnings
If you are doing testing with a database and you need to keep swapping out the database with an archived version, you will need to stop the SQL Server, drop the MDF and LDF in and then start the SQL Server again. You may or may not encounter a problem with accessing the database after doing that. If you cannot access the database then you need to drop the one that is displayed in the object explorer and re-attach it: Hence the script – it’s just faster that way. All of the query windows that you had open will complain about nothing listening at the other end of the pipe after you re-attach the database, just ignore the complaints and press the parse button (the blue check mark), this will jog the connection kind of like jangling the handle on a toilet.