9

Hi I have mdf and ldf files from a different SQL Server install from a machine i dont have access to now, and would like attach them to my current install to get at the data. When I try this, I get an error:

Cannot attach a database with the same name as an existing database.  (Microsoft.SqlServer.Smo)

This is because the mdf is named msdbdata, just like my current install. I don't know if there is even a way to rename this database now or what I can do. Any help would be appreciated. Thanks!

Drew
  • 275
  • 2
  • 3
  • 7
  • Well you really wouldn't be moving the MSDB database from one server to another server except in very specific circumstances, so I have to ask why you're trying to do this? – joeqwerty Jun 03 '11 at 19:22
  • I've had to do it to restore agent jobs, for example. – squillman Jun 03 '11 at 19:27
  • found myself having to do this when my laptop crashed and i moved the drive to another machine - obviously not a production env. – Simon May 01 '13 at 00:02

2 Answers2

9

You can rename the file, or put it in a different folder than your existing file, and then make sure to change the name of the database when you reattach it.

How are you trying to reattach it? Via SQL Server Management Studio or through tsql?

First, copy your mdf and ldf files to something new (name them relative to a new database you'll attach them as). Then in the Attach Databases screen:

  • Enter a new name for a (nonexisting) database in the Attach As field in the top frame
  • In the bottom frame, browse to both the new mdf file and the new ldf file you just created.

You should then get the new database attached.

squillman
  • 37,618
  • 10
  • 90
  • 145
0

This error can also occur when you're trying to attach a database you just removed from a different location when you're not paying attention and choose the wrong file.

user3071284
  • 159
  • 1
  • 1
  • 9
Simon
  • 1,301
  • 2
  • 15
  • 19