C # database backup and recovery

1. When configuring a user, we need to list all the database servers in the current LAN and all the databases on the specified server. The implementation code is as follows:

Obtain the database server list:
Public arraylist getserverlist ()
{
Arraylist alservers = new arraylist ();
Sqldmo. Application sqlapp = new sqldmo. applicationclass ();
Try
{
Sqldmo. namelist serverlist = sqlapp. listavailablesqlservers ();
For (INT I = 1; I <= serverlist. Count; I ++)
{
Alservers. Add (serverlist. Item (I ));
}
}
Catch (exception E)
{
Throw (new exception ("An error occurred while retrieving the Database Server LIST:" + E. Message ));
}
Finally
{
Sqlapp. Quit ();
}
Return alservers;
}

Obtains the Database List of the specified database server.
Public arraylist getdblist (string strservername, string strusername, string strpwd)
{
Servername = strservername;
Username = strusername;
Password = strpwd;

Arraylist aldbs = new arraylist ();
Sqldmo. Application sqlapp = new sqldmo. applicationclass ();
Sqldmo. svr SVR = new sqldmo. sqlserverclass ();
Try
{
SVR. Connect (servername, username, password );
Foreach (sqldmo. Database dB in SVR. databases)
{
If (db. Name! = NULL)
Aldbs. Add (db. Name );
}
}
Catch (exception E)
{
Throw (new exception ("database connection error:" + E. Message ));
}
Finally
{
SVR. Disconnect ();
Sqlapp. Quit ();
}
Return aldbs;
}

2. database backup and real-time progress display code:

Public bool backupdb (string strdbname, string strfilename, progressbar pgbmain)
{
Pbar = pgbmain;
Sqldmo. svr SVR = new sqldmo. sqlserverclass ();
Try
{
SVR. Connect (servername, username, password );
Sqldmo. Backup Bak = new sqldmo. backupclass ();
Bak. Action = 0;
Bak. initialize = true;
Sqldmo. backupsink_percentcompleteeventhandler pceh = new sqldmo. backupsink_percentcompleteeventhandler (STEP );
Bak. percentcomplete + = pceh;
Bak. Files = strfilename;
Bak. Database = strdbname;
Bak. sqlbackup (SVR );
Return true;
}
Catch (exception ERR)
{
Throw (new exception ("failed to back up the database" + err. Message ));
}
Finally
{
SVR. Disconnect ();
}
}

Private void step (string message, int percent)
{
Pbar. value = percent;
}

The two statements display the progress in Real Time:
Sqldmo. backupsink_percentcompleteeventhandler pceh = new sqldmo. backupsink_percentcompleteeventhandler (STEP );
Bak. percentcomplete + = pceh;
Step is the name of the private void step (string message, int percent) method above. It is used to display the current progress of the progress bar.

3. database recovery and killing Process Code:

Public bool restoredb (string strdbname, string strfilename, progressbar pgbmain)
{
Pbar = pgbmain;
Sqldmo. svr SVR = new sqldmo. sqlserverclass ();
Try
{
SVR. Connect (servername, username, password );
Sqldmo. queryresults QR = SVR. enumprocesses (-1 );
Int icolpidnum =-1;
Int icoldbname =-1;
For (INT I = 1; I <= QR. columns; I ++)
{
String strname = QR. get_columnname (I );
If (strname. toupper (). Trim () = "spid ")
{
Icolpidnum = I;
}
Else if (strname. toupper (). Trim () = "dbname ")
{
Icoldbname = I;
}
If (icolpidnum! =-1 & icoldbname! =-1)
Break;
}

For (INT I = 1; I <= QR. Rows; I ++)
{
Int LPID = QR. getcolumnlong (I, icolpidnum );
String strdbname = QR. getcolumnstring (I, icoldbname );
If (strdbname. toupper () = strdbname. toupper ())
SVR. killprocess (LPID );
}

Sqldmo. Restore res = new sqldmo. restoreclass ();
Res. Action = 0;
Sqldmo. restoresink_percentcompleteeventhandler pceh = new sqldmo. restoresink_percentcompleteeventhandler (STEP );
Res. percentcomplete + = pceh;
Res. Files = strfilename;

Res. Database = strdbname;
Res. replacedatabase = true;
Res. sqlrestore (SVR );
Return true;
}
Catch (exception ERR)
{
Throw (new exception ("failed to restore the database. Please close all programs connected to the database! "+ Err. Message ));
}
Finally
{
SVR. Disconnect ();
}
}

The statement retrieves a list of all processes:
Sqldmo. queryresults QR = SVR. enumprocesses (-1 );

The following statement finds and kills the process related to database Restoration:
Int icolpidnum =-1;
Int icoldbname =-1;
For (INT I = 1; I <= QR. columns; I ++)
{
String strname = QR. get_columnname (I );
If (strname. toupper (). Trim () = "spid ")
{
Icolpidnum = I;
}
Else if (strname. toupper (). Trim () = "dbname ")
{
Icoldbname = I;
}
If (icolpidnum! =-1 & icoldbname! =-1)
Break;
}

For (INT I = 1; I <= QR. Rows; I ++)
{
Int LPID = QR. getcolumnlong (I, icolpidnum );
String strdbname = QR. getcolumnstring (I, icoldbname );
If (strdbname. toupper () = strdbname. toupper ())
SVR. killprocess (LPID );
}