Adding .mdf and .ldf of an existing database by renaming them in SQL server 2005

sql-server-2005

I am trying to attach .mdf and .ldf file of an existing database with some change and different name.When i am attaching them , sql server automatically converting them to their original names(existing data base name) and giving out an error .

Best Answer

As far as my understanding goes that's kind of the way it is with detach and attach - this is both good and bad - good because database is substantially self contained within those files as it contains its own metadata notionally making moving a database easy, bad because there are scenarios where this isn't what one wants.

There are two obvious ways to deal with this depending on exactly what you are able to do:

  1. The first is to use backup and restore instead of detach/attach - at this point you can make quite a lot of changes.
  2. Second is to attach the database to a distinct server instance, rename it there and then detach and attach to the orignal instance.
Related Topic