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
