Change db to Offline with SQL-DMO from VBScript

Hi,

I have a VBScript that needs to run each night to detach and attach a database in MS SQL 2000 Server. The VBScript runs on the same server as SQL Server. The script runs fine when I manually fire it CScript, but when it ran in the middle of the night it didn't work because I'm guessing there were connections to the db. I would like to Force a disconnect and put the db in Offline status so that it will detach properly. I have attached my script with the names and passwords changed. Can someone tell me how to use SQL-DMO to accomplish this please? On MSDN it just describes the property and not how to use it. http://msdn.microsoft.com/en-us/library/aa260160(SQL.80).aspx

Thank you.
Dim oSQLServer 
Dim sDBUser
Dim sDBPwd 
Dim sDBName 
Dim sDBFile 
Dim sDBPath 
 
sDBName  = "MyDatabase" 
sDBUser  = "dbmaint"
sDBPwd 	 = "mypassword" 
sDBPath	 = "D:\Microsoft SQL Server\MSSQL\Data" 
sDBFile = sDBPath & "\" & sDBName & "_Data.mdf"
sLogFile = sDBPath & "\" & sDBName & "_Log.ldf"
 
 
Set oSQLServer = CreateObject("SQLDMO.SQLServer") 
oSQLServer.Connect "(local)", sDBUser, sDBPwd 
 
oSQLServer.DetachDB sDBName, True 
 
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objDeleteFile = objFSO.GetFile(sLogFile)
objDeleteFile.Delete
Set objDeleteFile = Nothing
Set objFSO = Nothing
 
oSQLServer.AttachDBWithSingleFile(sDBName, sDBFile) 
 
oSQLServer.Disconnect 
Set oSQLServer = Nothing

Open in new window

bangwebAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
That error is because you have an active connection to the database when you try to detach it, which is not allowed. Maybe an open Query Analyzer window or a connection from Enterprise Manager. You need to close/kill all connections before you can actually detach the database. It has nothing to do with the size.
0
 
mironCommented:
this should help if inserted between lines ##18-19, these two lines have an effect of immediate termination of all user connections to database noted in "sDBName"
oSQLServer.Databases("master").ExecuteImmediate( "ALTER DATABASE [" & sDBName & "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" )
oSQLServer.Databases("master").ExecuteImmediate( "ALTER DATABASE [" & sDBName & "] SET MULTI_USER WITH ROLLBACK IMMEDIATE" )

Open in new window

0
 
bangwebAuthor Commented:
Hi,

Your suggestion worked great on my smaller db but the larger db is still not working. I will put the error below so you can see what is happening.

The smaller db is 15MB for the MDF and the LDF will grow to a few MBs throughout the day.

The larger db is about 3.5GB for the MDF and the LDF will grow to about 3 to 4 GB throughout the day.

Thank you for your help.
Regards,
Steve
Script with change you suggested:
 
Dim oSQLServer 
Dim sDBUser
Dim sDBPwd 
Dim sDBName 
Dim sDBFile 
Dim sDBPath 
 
sDBName  = "MyDatabase" 
sDBUser  = "dbmaint"
sDBPwd 	 = "mypassword" 
sDBPath	 = "D:\Microsoft SQL Server\MSSQL\Data" 
sDBFile = sDBPath & "\" & sDBName & "_Data.mdf"
sLogFile = sDBPath & "\" & sDBName & "_Log.ldf"
 
 
Set oSQLServer = CreateObject("SQLDMO.SQLServer") 
oSQLServer.Connect "(local)", sDBUser, sDBPwd 
 
oSQLServer.Databases("master").ExecuteImmediate( "ALTER DATABASE [" & sDBName & "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" )
oSQLServer.Databases("master").ExecuteImmediate( "ALTER DATABASE [" & sDBName & "] SET MULTI_USER WITH ROLLBACK IMMEDIATE" )
 
oSQLServer.DetachDB sDBName, True 
 
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objDeleteFile = objFSO.GetFile(sLogFile)
objDeleteFile.Delete
Set objDeleteFile = Nothing
Set objFSO = Nothing
 
oSQLServer.AttachDBWithSingleFile(sDBName, sDBFile) 
 
oSQLServer.Disconnect 
Set oSQLServer = Nothing
 
ERROR:
 
Line: 22 (oSQLServer.DetachDB sDBName, True)
Char: 1
Error: [Microsoft][ODBC SQL Server Driver][SQL Server] Cannot detach the database 'MyDatabase' because it is currently in use.
Code: 800A0E75
Source: Microsoft SQL-DMO (ODBC SQLState: 42502)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.