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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ZberteocCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.