Customizable database backup and recovery program Tashanzhishi [Original]
Keyword Database backup recovery
Source
When we do the database system program, often need to do a database backup and recovery procedures for customers, especially for some unprofessional database users, this program is essential, and the operation must be simple enough. Because in many systems, the backup recovery function of the database is similar, so we'd better do a common database backup recovery program, so that we do not have to develop a set of each system.
To develop such a system, I personally believe that the following requirements should be met:
1. Backup recovery operations should have a history (you must have a backup list that lists information about the backup files) to make it easier for users to find previous backups.
2. For each backup and restore, users should be allowed to record the reasons for backup 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, preferably similar to file replication, and allow users to recover from the backup list for backups that have already been made.
5. Even if the database being recovered is in use for some reason, it is important to allow the user to recover (this is essential because you cannot expect the user to guarantee the exclusive nature of the database).
6. Displays the current progress of the backup or recovery in real time.
To meet the above requirements, I think we should design the system like this:
1. For every database backup and recovery, we all write down the database server name, database name, backup file full path name, backup time, backup or recovery reasons, and so on, and save this information in the form of XML, below is my backup file instance:
<?xml version= "1.0" encoding= "GB2312"?>
<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>
2. This can be done for the user's configuration:
<?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, no practical use), the backup history maximum number of records, the database server name backed up, the database name of the backup, the username, the password (encrypted), and so on, are recorded in sequence. We automatically apply this information to the user interface as soon as the program is started, so that the user is not reset.
3. Backup when we take a direct backup to the file, the user only need to use the Save File dialog box to specify the location and file name to be backed up, the rest of the work through the program completed, restore the same, simply by opening a file dialog box to specify the files recovered from it.
4. When we recover, we first kill all the user threads associated with the database being recovered, and then recover, so that there is no recovery error due to database exclusivity.
5. For real-time display of backup and recovery progress problems, we take the SQL-DMO callback function to implement the way.
The following is a code implementation of related technical difficulties (because of personal preference, which is implemented in C # code form):
1. In the user's configuration, we need to list all the database servers in the current LAN, and to list all the databases for the specified server, the implementation code is as follows:
To get 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 (New Exception ("Fetch database server List error:" +e.message));
}
Finally
{
Sqlapp.quit ();
}
return alservers;
}
Gets the list of databases for 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. SQL Server 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 ("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 strdbname,string strFileName, ProgressBar pgbmain)
{
Pbar = Pgbmain;
SQLDMO. SQL Server 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 ("Backup Database Failed" +err.) message));
}
Finally
{
Svr. DisConnect ();
}
}
private void step (string message,int percent)
{
Pbar.value = percent;
}
Where these two statements achieve the real-time display of progress:
SQLDMO. Backupsink_percentcompleteeventhandler PCEH = new SQLDMO. Backupsink_percentcompleteeventhandler (step);
Bak. PercentComplete + = PCEH;
Step is the name of the method above private void step (string message,int percent), which is used to display the current progress of the progress bar.
3. Database recovery and the code that kills the process:
public bool RestoreDB (string strdbname,string strFileName, ProgressBar pgbmain)
{
Pbar = Pgbmain;
SQLDMO. SQL Server 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 (The new Exception ("RESTORE Database failed," Close all programs that connect to the database.) "+err. message));
}
Finally
{
Svr. DisConnect ();
}
}
Where this statement gets all the process lists:
SQLDMO. QueryResults qr = svr. EnumProcesses (-1);
The following statement finds and kills the process associated with the database to be restored:
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);
}
