When we do a database system, you often need to make a backend and recovery program for customers, especially for some non-professional database users, this program is more essential, and the operation must be simple enough. Because in many systems, the database's backup recovery feature is similar, so we'd better be a common database backup recovery program so that you don't have to develop a set every system.
To develop such a system, I personally think should meet the following requirements:
1. Backup recovery operations should have historical records (there must be a backup list, listing information about the backup file), which is easy to find previous backups.
2. For each backup and recovery, users should be allowed to record backups and recovery.
3. The system should allow the user to make a simple configuration, and the configuration can be saved.
4. Backup and recovery should be simple enough, it is bestcoming copy of files. For backups that have been made, users should allow users to recover from backup lists.
5. Even for some reason, the database to be recovered is taken, and the user should be allowed to recover (this is important because you can't expect the user to ensure the exclusiveity of the database).
6. Display the current progress of backup or recovery in real time.
To reach the above requirements, I think we should design the system like this:
1. For each database backup and recovery, we write down the database server name, database name, backup file full path name, backup time, backup or restore reason, etc., and save this information in XML, Here is my backup file instance:
2. For the user's configuration, it can be performed:
The name of the backup program is recorded in turn (displayed in the title bar of the backup form, no practical use), backup history maximum number, backup database server name, backup database name, user name, password (have been encrypted) information. When the program is started to start start, we automatically apply this information to the user interface, so you don't have to reset it. 3. When you are backup, we take the way to back up to the file. Users only need to use the Save File dialog to specify the location and file name to be backed up, the rest of the job is completed, and the restoration is also the same, simply specify from the open file dialog. Among them, the files are restored.
4. When we recover, let's kill all user threads associated with the database you want to recover, and then recover, so there will be no recovery errors due to database exclusiveity.
5. For real-time display of the progress of the backup and recovery, we take the way SQL-DMO's callback function is implemented.
Below is a code implementation of the related technical difficulties (because personal preferences are implemented in the form of C # code):
1. When the user's configuration, we need to list all database servers in the current LAN, and to list all the databases of the specified server, the code is as follows:
Take the list of database servers:
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 ("Note the database server list error:" E.MESSAGE));
}
Finally
{
SQLAPP.QUIT ();
}
Return alservers;
}
Get the list of database servers to specify the database server
Public ArrayList Getdblist (String StruserName, String Strpwd)
{
ServerName = strservername;
Username = strusername;
Password = strpwd;
ArrayList aldbs = new arraylist ();
SqldMo.application sqlapp = new sqldmo.applicationclass ();
Sqldmo.sqlserver 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 ("connection database error:" E.MESSAGE));
Finally
{
Svr.disconnect ();
SQLAPP.QUIT ();
}
Return ALDBS;
}
2. Database backup and real-time progress display code:
Public Bool Backupdb (String Strfilename, ProgressBar PGBMain)
{
PBAR = PGBMain;
Sqldmo.sqlserver 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 " Err.Message));
}
Finally
{
Svr.disconnect ();
}
}
Private Void Step (String Message, INT Percent)
{
PBAR.VALUE = percent;
}
Among them, these two statements have realized real-time display of progress:
SqldMo.Backupsink_PercentCompleteEventHandler PCEH = New SqldMo.Backupsink_PercentCompleteEventHandler (STEP);
Bak.PercentComplete = PCEH;
STEP is the method name of the Private Void Step (String Message, INT Percent), which is used to display the current progress of the progress bar.
3. The recovery of the database and the code of killing the process:
Public Bool Restoredb (String Strfilename, ProgressBar PGBMain)
{
PBAR = PGBMain;
Sqldmo.sqlserver 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.column; i )
{
String strname = qr.get_columnname (i);
IF (Strname.toupper (). Trim () == "SPID")
{
Icolpidnum = i;}
Else if (Strname.toupper (). Trim () == "DBNAME")
{
ICOLDBNAME = I;
}
Icolpidnum! = -1 && icholdbname! = -1)
Break;
}
For (int i = 1; i <= qr.rows; i )
{
INT LPID = Qr.GetColumnlong (i, icolpidnum);
String strdbname = qr.getColumnstring (i, icholdbname);
IF (strDbname.toupper () == strDbname.toupper ())
Svr.killProcess (LPID);
}
SqldMo.Restore Res = New SqldMo.restoreClass ();
Res. activity = 0;
SqldMo.RestoreSink_PercentCompleteEventHandler PCEH = New SqldMo.restsink_PercentCompleteEventHandler (STEP);
Res.Percentcomplete = PCEH;
Res.files = strfilename;
Res. Database = strdbname;
Res. regratedDatabase = true;
Res.sqlrestore (SVR);
Return True;
}
Catch (Exception Err)
{
Throw ("Restore Database Failed, turn off all the programs connected to the database!" Err.Message));
}
Finally
{
Svr.disconnect ();
}
}
Where this statement gets all the list of processes:
SqldMo.queryResults qr = svr.enumprocesses (-1);
The following statement finds and wants to recover the process related to the database and kill:
INT iColpidnum = -1;
INT iCOLDBNAME = -1;
For (int i = 1; i <= qr.column; i )
{
String strname = qr.get_columnname (i);
IF (Strname.toupper (). Trim () == "SPID")
{
Icolpidnum = i;
}
Else if (Strname.toupper (). Trim () == "DBNAME")
{
ICOLDBNAME = I;
}
Icolpidnum! = -1 && icholdbname! = -1)
Break;
}
For (int i = 1; i <= qr.rows; i )
{
INT LPID = Qr.GetColumnlong (i, icolpidnum);
String strdbname = qr.getColumnstring (i, icholdbname);
IF (strDbname.toupper () == strDbname.toupper ())
Svr.killProcess (LPID);
}
