[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

After restore MSSQL Database by VB program, the database cannot login again.

My VB Program contains a Restore database function :

Public Function ExecuteSQLOK(CN As ADODB.Connection, SQL As String, Optional UseTransaction As Boolean = False) As Boolean

' Execute an SQL statement
On Error Resume Next
If UseTransaction Then CN.BeginTrans
CN.Execute SQL, , adCmdText + adExecuteNoRecords

If UseTransaction Then CN.CommitTrans

If Err.Number <> 0 Then
    If UseTransaction Then
    End If
    ExecuteSQLOK = False

    ExecuteSQLOK = True
End If

End Function

Public Function RestoreDBOK(CN As ADODB.Connection, DatabaseName As String, SourceFile As String) As Boolean

' Restore a database don't try master

Dim SQL As String
Dim OK

SQL = "USE master" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'TRUE'" + vbCrLf
SQL = SQL + "RESTORE DATABASE " + DatabaseName + " FROM DISK = '" + SourceFile + "'" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'FALSE'" + vbCrLf

RestoreDatabaseOK = ExecuteSQLOK(CN, SQL)

End Function

However, after restore the database. I cannot connect to this database again.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'TABLENAME'. Login fails.

Please help~~~

Thank you.
1 Solution

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now