Application: VB6 COM.EXE, VB .NET Framework 1.1 VS 2003 DLLs
Database: MSDE 2000
A) Database Access used by new components: ODBCDriver / .NET ODBC dataprovider / ADO.NET
B) Legacy database access, used in the backup restore component: ODBC Driver / ADODB (COM)
The application provides a "backup/restore" function, since MSDE does not have an interface for this.
1.) As we experienced, the restore only works if the database is in SINGLE_USER mode. Correct?
2.) To be able to switch to this mode, we sometimes need to remove other active sessions from the database.
We think these sessions are the result of connection pooling of Database Access A) components, so they also need to be removed from the connection pool.
3.) We also need to identify the sessions that we will kill so we do not kill the session that is
used by our function.
Thank you for your assistance.
For restore we use:
Dim gADO As ADODB.Connection
Dim sSQL As String
sSQL = "ALTER DATABASE " & sCurrentDBName & " SET SINGLE_USER"
bDBModeWasChanged = True
Catch ex As Exception
sSQL = "USE master; RESTORE DATABASE " & sCurrentDBName & _
" FROM DISK = '" & sRestoreFileName & "'" & vbCrLf & _
"USE " & sCurrentDBName & ";"