Original link
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 that the following requirements should be met: 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:
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, implemented in this C # code): 1. When the user is configured, we need to list all database servers in the current LAN, and to list all the databases of the specified server, implement the code as follows: obtain 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 ("NEW EXCEPTION (" Take the Database server list error: " E.MESSAGE));} finally {sqlapp.quit ();} return alservers;} acquire a designated database Server Database list public arraylist getdblist (string strusername, string strusername, string strupwd) {servername = strservername; username = strusename; password = strupwd;
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 display of the progress of the code: public bool BackUPDB (string strDbName, 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 ("Backup Database Failed" Err.Message));} Finally {Svr.disconnect () }}
private void Step (string message, int percent) {PBar.Value = percent;} wherein these two statements implement real-time display of progress: SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler (Step); bak.PercentComplete = pceh Step is the method name of the above private void step (intencent), which is used to display the current progress of the progress bar.
3. Code recovery process and kill the database: public bool RestoreDB (string strDbName, string strFileName, ProgressBar pgbMain) {PBar = pgbMain; SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass (); try {svr.Connect (ServerName, UserName, Password; sqldmo.queryresults = svr.enumprocesses (-1); int icolpidnum = -1; int icholdbname = -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") {icldbname = i ;} if (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.Action = 0; SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler (Step); res.PercentComplete = PCEH; Res.Files = StrfileName;
RES.DATABASE = strdbname; res. replacedDatabase = true; res.sqlrestore (SVR); return true;} catch (exception err) {throw ("Restore Database Failed, turn off all and this database connection!" Err.Message));} finally {svr.disconnect ();}}
This statement has achieved all processes list: SQLDMo.QueryResults qldmo.QueryResults QR = SVR.EnumProcesses (-1); the following statement finds and to recover the database-related process and kill: int iclpidnum = -1; int icholdbname = -1; For (int i = 1; i <= qr.columns; i ) {string strname = qr.get_columnname (i); if (Strname.toupper (). Trim () == "spid") {iclpidnum = i;} Else if (Strname.toupper (). Trim () == "DBNAME") {icldbname = i;} if (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);
The above is the key code of the database backup and recovery. With these codes, we achieve a better database backup recovery feature is also no problem, for example, you can easily implement timeline backup or in accordance with a certain loop policy.
