ShelfieldCollege
asked on
Help restoring database
Hi folks, I'm using the below function which I knocked together from a couple of examples to try and restore a database back into MS SQL. The function starts running, but the VB app just hangs and doesn't resume processing with any reasonable time (at the moment just doing 50MB restore which takes couple of seconds usually).
Any help would be greatly appreciated... Basically the DB I'm trying to restore is one that can be connected to multiple times by the same user.
Private Function RestoreBackup(sFile As String) As Boolean
On Error GoTo errH
Dim dbHost As String
Dim dbName As String
dbHost = cFMSettings("database|host ")
dbName = cFMSettings("database|name ")
Dim oConn As New ADODB.Connection
Dim rs As Recordset
Dim sConn As String
sConn = "Driver={SQL Server};" & _
"Server=" & dbHost & ";" & _
"Database=" & dbName & ";" & _
"Uid=user;" & _
"Pwd=pass"
oConn.Open sConn
Dim sSQL As String
sSQL = "USE master;" & vbCrLf & _
"ALTER DATABASE " & dbName & " SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;" & vbCrLf & _
"RESTORE DATABASE " & dbName & " FROM DISK = '" & sFile & "';" & vbCrLf '& _
"ALTER DATABASE " & dbName & " SET MULTI_USER WITH ROLLBACK IMMEDIATE;"
Set rs = oConn.Execute(sSQL)
'If RestoreDatabaseOK(oConn, dbName, sFile) = True Then
' RestoreBackup = True
'Else
' RestoreBackup = False
'End If
RestoreBackup = True
Exit Function
errH:
If Err.Number = -2147217900 Then
Resume
Else
MsgBox Err.Number & " " & Err.Description
End If
RestoreBackup = False
Exit Function
End Function
The if err.number = -2147217900 part is supposedly there to enable to code to continue running when it receives the "Changed context to.. " message which apparently is just an information message rather than an error.
Thanks in advance
Any help would be greatly appreciated... Basically the DB I'm trying to restore is one that can be connected to multiple times by the same user.
Private Function RestoreBackup(sFile As String) As Boolean
On Error GoTo errH
Dim dbHost As String
Dim dbName As String
dbHost = cFMSettings("database|host
dbName = cFMSettings("database|name
Dim oConn As New ADODB.Connection
Dim rs As Recordset
Dim sConn As String
sConn = "Driver={SQL Server};" & _
"Server=" & dbHost & ";" & _
"Database=" & dbName & ";" & _
"Uid=user;" & _
"Pwd=pass"
oConn.Open sConn
Dim sSQL As String
sSQL = "USE master;" & vbCrLf & _
"ALTER DATABASE " & dbName & " SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;" & vbCrLf & _
"RESTORE DATABASE " & dbName & " FROM DISK = '" & sFile & "';" & vbCrLf '& _
"ALTER DATABASE " & dbName & " SET MULTI_USER WITH ROLLBACK IMMEDIATE;"
Set rs = oConn.Execute(sSQL)
'If RestoreDatabaseOK(oConn, dbName, sFile) = True Then
' RestoreBackup = True
'Else
' RestoreBackup = False
'End If
RestoreBackup = True
Exit Function
errH:
If Err.Number = -2147217900 Then
Resume
Else
MsgBox Err.Number & " " & Err.Description
End If
RestoreBackup = False
Exit Function
End Function
The if err.number = -2147217900 part is supposedly there to enable to code to continue running when it receives the "Changed context to.. " message which apparently is just an information message rather than an error.
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ahh I thought just using USE MASTER would free up the database, I'll give it a try, thanks.
ASKER
Hi ya, thanks for your comment. After a little more work (turns out the user im connecting as didn't have permission to restore the database which messed it up again, so now my application prompts the user for their SA password to do the restore.
And all works well, cheers :)
And all works well, cheers :)
ASKER
Anyways, thats besides the point, I'd like to do it using the SQL already there or something similar if that's possble
Cheers