C # customizable database backup and recovery procedures

Customizable database backup and recovery program tashanzhishi [original]
Keyword Database Backup Recovery
Source

When we develop database system programs, we often need to prepare a database backup and recovery program for our customers, especially for non-professional database users, which is even more essential, the operation must be simple enough. In many systems, database backup and recovery functions are similar. Therefore, we 'd better develop a general database backup and recovery program so that we don't have to Develop a set for every system.

To develop such a system, I personally think it should meet the following requirements:
1. the Backup recovery operation should have a historical record (there must be a backup list to list information about the backup files), so that you can easily find the previous backup.
2. Users should be allowed to record the reasons for backup and recovery for each backup and recovery.
3. The system should allow users to perform simple configuration and save the configuration.
4. backup and recovery should be simple enough. It is best to copy files like files. For existing backups, users should be allowed to recover from the backup list.
5. Even if the database to be recovered is in use for some reason, it should allow the user to recover (this is important because you cannot expect the user to ensure the database's independence ).
6. The current backup or recovery progress is displayed in real time.
To meet the above requirements, I think we should design the system like this:
1. for each database backup and recovery, we will write down the information such as the database server name, database name, full path name of the backup file, backup time, and the reason for the backup or recovery, save the information in XML format. Below is an example of a backup file:
<? XML version = "1.0" encoding = "gb2312"?>
<History>
<Bak>
<Time> 20040205 10:41:21 </time>
<Dbname> hrmjx4 </dbname>
<Des> test backup </des>
<Path> E:/ricatex2003.bak </path>
</Bak>
<Bak>
<Time> 20040205 10:43:58 </time>
<Dbname> hrmjx4 </dbname>
<Des> weekly routine backup </des>
<Path> E:/hrmjx4040205.bak </path>
</Bak>
</History>
2. Configure the user as follows:
<? XML version = "1.0" encoding = "gb2312"?>
<Set>
<Appname> Database Backup </appname>
<Bakcount> 20 </bakcount>
<Servername>. </servername>
<Dbname> book </dbname>
<Username> SA </username>
<Password> 2iuc94tkpsg = </password>
</Set>
The name of the backup program (displayed in the title bar of the backup form for no practical use), the maximum number of backup history records, the name of the backup database server, the name of the backup database, and the user name are recorded in sequence, password (encrypted) and other information. We automatically apply the information to the user interface when the program is started, so that you do not have to reset it.
3. during backup, you can directly back up the data to a file. You only need to use the save file dialog box to specify the location and file name of the backup. The rest of the work is done through the program and the recovery is the same, you only need to specify the files to be restored from in the open file dialog box.
4. When restoring, we first kill all the user threads associated with the database to be recovered, and then recover it. In this way, there will be no recovery errors caused by database exclusiveness.
5. For the real-time display of backup and recovery progress problems, we adopt the callback function of SQL-DMO implementation.

The following is the code implementation of related technical difficulties (this is implemented in the C # code format due to personal preferences ):
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 );
}