?
Solved

Change db to Offline with SQL-DMO from VBScript

Posted on 2008-11-10
3
Medium Priority
?
957 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:bangweb
3 Comments
 
LVL 9

Expert Comment

by:miron
ID: 22929257
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
 

Author Comment

by:bangweb
ID: 22930066
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
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 22950929
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question