Pausing SQL Server

Pausing SQL Server

Pausing SQL Server prevents new users from logging in and gives you time to send a message to current users asking them to complete their work and log out before you stop the server. You can pause SQL Server using the Enterprise Manager or by creating your own program with this function which uses SQL-DMO objects. First, add a reference to sqldmo.rll in your VB project. This file can be found in BinnResources1033sqldmo.rll under the SQL Server 7.0 directory. Now add this code and declaration to your form’s code:

 Private WithEvents oSqlServer As SQLDMO.SQLServerPrivate Function PauseSqlServer(ByVal strServerName As String, strErrorMsg As String) As BooleanStatic boolFunctionExecuting As Boolean    If Not boolFunctionExecuting Then        BoolFunctionExecuting = True        Set oSqlServer = New SQLServer        With oSqlServer            .Name = strServerName            Do While .Status <> SQLDMOSvc_Paused                Select Case .Status                    Case SQLDMOSvc_Running                        .Pause                    Case SQLDMOSvc_Pausing                        DoEvents                    Case SQLDMOSvc_Unknown                        strErrorMsg = "SqlServer in unknown state"                        Exit Do                End Select            Loop            If .Status = SQLDMOSvc_Paused Then                PauseSqlServer = True            Else                PauseSqlServer = False            End If        End With        Set oSqlServer = Nothing        BoolFunctionExecuting = False    End IfEnd Function


Share the Post: