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
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)
Set objDeleteFile = Nothing
Set objFSO = Nothing
Set oSQLServer = Nothing