SQLTeam.com | Weblogs | Forums

How to attach a database that hasn't been detached?


How to attach a database that hasn't been detached?


Bit hard to be sure I've understood the question.

You have a database that is attached, and thus "in use", on a server and you want to attach that database eigher again to that server, or to another server?

So you want a copy of an existing database?

I would use Backup and Restore (to a new name / server)


I have a copy of the database, but it hasn't been detached. I would like to attach to another server.


If you have .mdf file(s) and .ldf file(s), you should use command:
CREATE DATABASE {db_name} ( {file_details_as_per_sql_syntax} ) FOR ATTACH

For example, if you have existing db files "c:\data\db_name.mdf" and "c:\data\db_name_log.ldf":
( NAME = new_db_name, FILENAME = 'c:\data\db_name.mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 20MB )
LOG ON ( NAME = new_db_name_log, FILENAME = 'c:\data\db_name_log.ldf', MAXSIZE = UNLIMITED, FILEGROWTH = 4MB )


What form is the "copy" of the database?

If its a file (preferably a pair of files xxx.MDF and xxx.LDF) that was just copied when the SQL Service was stopped then you can attach it.

If its those files, but SQL Service was running at the time the copy was made then you can try to attach them but they may well be incomplete / inconsistent

If the file was created using BACKUP then RESTORE it.


I forgot one critical thing:

Keep a separate copy of all files you attempt to attach!
Reason: A failed attach could render the file unable to be attached again later. Therefore, if you attempt to attach your only copy of the file, you could permanently render it unable to be attached.
If the attach fails, copy the original files again and re-try the attach.


I just solved the issue just opening the management studio in Administrator mode.