Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Attach and Detach MDF file in MSDE SQL SERVER

Status
Not open for further replies.

anhnguyen1999

Programmer
Apr 22, 2002
5
US
Hi all,
I tried to detach a current database so that I can attach it with different set of database file (.mdf and .ldf). But it gave me the error
"Cannot detach the database 'TESTING' because it it currently in use"

Anyone know how to fix the problem?

Here is my code

Set goSQLServer = New SQLDMO.SQLServer
goSQLServer.LoginTimeout = -1 '-1 default (60) seconds
' Connect to the Server
' Use NT Authentication
goSQLServer.LoginSecure = True
goSQLServer.AutoReConnect = False
' Now Connect
goSQLServer.Connect "(local)"
'after login success msg, then I try to detach database 'TESTING' that current attached to "c:\testing1.mdf" and "c:\testing1.ldf"
goSQLServer.DetachDB "TESTING"

'planning to attach database file after detaching
goSQLServer.AttachDB "TESTING", "c:\testing2.mdf"
goSQLServer.AttachDB "TESTING", "c:\testing2.ldf"

Thanks a lot
 
Not sure about your code, but for detaching a database you need to be using Master database at that time. If any process is currently using the database you are using to detach, it wouldn't let you detach it just by using sp_detach_db.

In the Enterprise Manager there is an option where it prompts you that there are some open connections to the database and if you still want to detach it. There may be a SQL equivalent of it that you can use.

K.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top