Listing 1

expect to get an object reference error here if the
target database isn’t found.
If ucase(oSvr.Databases(“volunteerSQL”).Name) <> “VOLUNTEERSQL” Then
If intLoopCount = à˜ Then
strS = “[“ & App.Path & “\VolunteerSQL.mdf” & “]”
oSvr.AttachDB “volunteerSQL”, strS
Sleep 1à˜à˜à˜ ‘ Wait 1 second to give the server a chance to install the database.
‘ Run a script to install users and permissions.
strS = “osql /U sa /P “ & strPW & “ /i “”” & _
App.Path & “\SetPermissions.sql” & “”””
RecordError strS
Shell strS, vbNormalFocus
Sleep 8à˜à˜à˜ ‘ Wait 8 seconds before retrying.
Else
Sleep 1à˜à˜à˜
Beep
End If
oSvr.Databases.Refresh
intLoopCount = intLoopCount + 1
Else
Exit Do
End If

Loop Until intLoopCount > 1à˜
strS = “[“ & App.Path & “\VolunteerSQL.mdf” & “]”
oSvr.AttachDB “volunteerSQL”, strS

strS = “osql /U sa /P “ & strPW & “ /i “”” & App.Path _
& “\SetPermissions.sql” & “”””
Shell strS, vbNormalFocus
Sleep 8à˜à˜à˜ ‘ Wait 8 seconds before retrying.
Else
Sleep 1à˜à˜à˜ ‘ Wait 1 more second (1à˜ times).
Beep
End If

oSvr.Databases.Refresh
intLoopCount = intLoopCount + 1

Loop Until intLoopCount > 1à˜
If intLoopCount < 1à˜ Then
Quit:
StartSQLServer = True
Exit Function
Else
QuitWithError:
StartSQLServer = False
MsgBox “Could not run batch to install DB. Program ending.”
RecordError “attach.SQL not completed.” ‘ My routine to log errors to a file.
Exit Function
End If

SSSError:
Debug.Print Err.Number, Err.Description
Select Case Err.Number
Case -2147199728
‘ The name ‘VolunteerSQL’ was not found in the Databases collection.
Resume Next
Case -2147216399 ‘ The physical file name ‘<>’ may be incorrect.
RecordError ‘ My routine to log errors of all kinds to a file.
MsgBox “The database file is missing. Program cannot …”
Resume QuitWithError
Case -2147à˜2384à˜ ‘ The server is already running.
Resume quit
Case -21472215à˜4 ‘ Timeout trying to start database?
i = MsgBox(“Could not start database engine. …”)
If i = vbRetry Then Resume
RecordError ‘ My routine to log errors of all kinds to a file.
Resume QuitWithError
Case -21472à˜4362
RecordError
MsgBox “The database has been paused. …”
End Select

Resume QuitWithError

Public Sub ShutDownServer()
On Error Resume Next
If oSvr.Status = SQLDMOSvc_Running Then oSvr.Shutdown
‘ Shut down SQL Server.
End Sub

Public Sub BackupDatabase()
strS = “BACKUP DATABASE VolunteerSQL TO [VolunteerSQL Backup Device] WITH INIT” ‘ Overlay existing backup.
cn.Execute strS, , adExecuteNoRecords
daLastBackup = Now
SaveSetting “ChurchVolunteer”, “Backup”, “Last”, daLastBackup
End Sub

ALTER DATABASE volunteersql SET single_user WITH ROLLBACK
after 1à˜

Public Function RestoreDatabase() As Boolean
On Error GoTo RDEH
‘ Ensure that there are no connections in the pool or elsewhere.
oSvr.Shutdown
Sleep 2à˜à˜à˜ ‘ Give the server a chance to shut down before you try a restart.
Set oSvr = New SQLDMO.SQLServer
oSvr.Start True, “(local)”, “SA”, strPW
oSvr.Databases(“VolunteerSQL”, “DBO”).DBOption.SingleUser = True
strS = “RESTORE DATABASE VolunteerSQL FROM [VolunteerSQL Backup Device]”
oSvr.ExecuteImmediate strS
oSvr.Databases(“VolunteerSQL”, “DBO”).DBOption.SingleUser = False
oSvr.Close
RestoreDatabase = True
Quit:
Exit Function
RDEH:
Select Case Err
Case -21472158à˜2 ‘ Server shut down by request.
Resume Next ‘ We expect these errors when the server is shut down.
Case -21472215à˜4 ‘ SQL Server does not exist or access denied.
intLoopCnt = intLoopCnt + 1
If intLoopCnt > 1à˜ Then
RecordError
MsgBox “Couldn’t restart the database to perform Restore.”
RestoreDatabase = False
Resume Quit
Else
Sleep 2à˜à˜à˜ ‘ Wait a little longer to let the server restart.
Beep
Resume
End If
Case Else
Debug.Print Err, Err.Description
MsgBox “Unexpected error attempting restore. See error log.”
RecordError
RestoreDatabase = False
Resume ‘Quit
End Select
End Function