SQL customizable database backup and recovery programs

There are three types of SQL backups:
Full backup/differential backup/log backup

The preceding three backups can be performed independently.
On Restoration, full backup files can be restored independently.
To restore a differential backup, you must restore the last full backup before the differential backup.
To restore a log backup, you must first restore the last full backup before the differential backup, and then restore the log backup after the full backup Based on the backup time.

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 ++)

 

/*************************************** **************************************** *****
The stored procedure for backup and restoration of the ms SQL database was written last time.
For details, see (http://www.cnblogs.com/aierong/archive/2004/05/10/8789.aspx)
I felt that the features were not complete, so I wrote a new enhanced version, which was successfully tested!
Publish the code for sharing.
If any bug found, please advise or email: aierong@vip.sina.com

 

/** // * Back up the database */
Create proc pr_backup_db
@ Flag varchar (20) Out,
@ Backup_db_name varchar (128 ),
@ Filename varchar (1000) -- path + file name
As
Declare @ SQL nvarchar (4000), @ par nvarchar (1000)
If not exists (
Select * from Master .. sysdatabases
Where name = @ backup_db_name
)
Begin
Select @ flag = 'db not exist'/** // * The database does not exist */
Return
End
Else
Begin
If right (@ filename, 1) <> ''and charindex ('', @ filename) <> 0
Begin
Select @ par = '@ filename varchar (1000 )'
Select @ SQL = 'backup database' + @ backup_db_name
+ 'To disk = @ filename with init'
Execute sp_executesql @ SQL, @ par, @ filename
Select @ flag = 'OK'
Return
End
Else
Begin
Select @ flag = 'file type error'/** // * parameter @ filename input format Error */
Return
End
End

Go

 

/** // * Create a function to obtain the file path */
Create Function fn_getfilepath (@ filename nvarchar (260 ))
Returns nvarchar (260)
As
Begin
Declare @ file_path nvarchar (260)
Declare @ filename_reverse nvarchar (260)
Select @ filename_reverse = reverse (@ filename)
Select @ file_path = substring (@ filename, 1, Len (@ filename) + 1-charindex ('', @ filename_reverse ))
Return @ file_path
End

Go

 

 

/** // * Restore the database */
Create proc pr_restore_db
/**//*
------------------------------------------------
Create Time: 2004-03-20
Update Time:
Author: aierong
Remark: Restore the database
 
------------------------------------------------
*/
/** // * Indicates the running status of the process, which is the input parameter */
@ Flag varchar (20) Out,
/** // * Name of the data to be restored */
@ Restore_db_name nvarchar (128 ),
/** // * Path of the backup file storage + name of the backup file */
@ Filename nvarchar (260)
As
/** // * Return the system stored procedure xp_mongoshell running result */
Declare @ proc_result tinyint
/** // * Number of cycles */
Declare @ loop_time smallint
/** // * @ Maximum number of IDS columns in the TEM table */
Declare @ max_ids smallint
/** // * Original database storage path */
Declare @ file_bak_path nvarchar (260)
/** // * File storage mark */
Declare @ flag_file bit
/** // * Path of the database master file */
Declare @ master_path nvarchar (260)
Declare @ SQL nvarchar (4000), @ par nvarchar (1000)
Declare @ SQL _sub nvarchar (4000)
Declare @ SQL _cmd nvarchar (100)
Declare @ SQL _kill nvarchar (100)
/**//*
Determine the validity of the @ filename file format to prevent users from entering invalid file names such as D: or C:.
The parameter @ Filename must contain ''and does not end ''.
*/
If right (@ filename, 1) <> ''and charindex ('', @ filename) <> 0
Begin
Select @ SQL _cmd = 'dir' + @ filename
Exec @ proc_result = Master .. xp_cmdshell @ SQL _cmd, no_output
/** // * Code returned by the system stored procedure xp_cmdshell: 0 (successful) or 1 (failed )*/
If (@ proc_result <> 0)
Begin
/** // * The backup file does not exist */
Select @ flag = 'not exist'
/** // * Exit process */
Return
End
/** // * Create a temporary table and save the result set consisting of a list of databases and log files contained in the backup set */
Create Table # TEM (
/** // * Logical name of the file */
Logicalname nvarchar (128 ),
/** // * The physical name or operating system name of the file */
Physicalname nvarchar (260 ),
/** // * Data file (d) or log file (l )*/
Type char (1 ),
/** // * Name of the file group containing the file */
Filegroupname nvarchar (128 ),
/** // * Current size (in bytes )*/
[Size] numeric (20, 0 ),
/** // * Maximum allowed size (in bytes )*/
[Maxsize] numeric (20, 0)
)
/**//*
Create a table variable. The table structure is basically the same as that of a temporary table.
There are two more columns,
Column IDS (auto-increment Number Column ),
Column file_path: path for storing files
*/
Declare @ TEM table (
/** // * Auto-increment Number Column */
IDS smallint identity,
Logicalname nvarchar (128 ),
Physicalname nvarchar (260 ),
File_path nvarchar (260 ),
Type char (1 ),
Filegroupname nvarchar (128)
)
Insert into # TEM
Execute ('Restore filelistonly from disk = ''' + @ filename + '''')
/** // * Import the temporary table to the table variable and calculate the corresponding path */
Insert into @ TEM (logicalname, physicalname, file_path, type, filegroupname)
Select logicalname, physicalname, DBO. fn_getfilepath (physicalname), type, filegroupname
From # TEM
If @ rowcount> 0
Begin
Drop table # TEM
End
Select @ loop_time = 1
/** // * @ Maximum number of IDS columns in the TEM table */
Select @ max_ids = max (IDS)
From @ TEM
While @ loop_time <= @ max_ids
Begin
Select @ file_bak_path = file_path
From @ TEM where IDs = @ loop_time
Select @ SQL _cmd = 'dir' + @ file_bak_path
Exec @ proc_result = Master .. xp_cmdshell @ SQL _cmd, no_output
/** // * Code returned by the system stored procedure xp_cmdshell: 0 (successful) or 1 (failed )*/
If (@ proc_result <> 0)
Select @ loop_time = @ loop_time + 1
Else
/** // * The original storage path of the data file before the backup is not found. Exit the loop */
Break
End
Select @ master_path =''
If @ loop_time> @ max_ids
/** // * The original storage path of the data file before backup exists */
Select @ flag_file = 1
Else
Begin
/** // * The original storage path of the data file before backup does not exist */
Select @ flag_file = 0
Select @ master_path = DBO. fn_getfilepath (filename)
From master.. sysdatabases
Where name = 'master'
End
Select @ SQL _sub =''
/** // * Type = 'D' indicates the data file, and type = 'l' indicates the log file */
/** // * @ Flag_file = 1. The new database file is still stored in the original path. Otherwise, the storage path is the same as the master database path */
Select @ SQL _sub = @ SQL _sub + 'move ''' + logicalname + ''' '''
+ Case type
When 'd 'then case @ flag_file
When 1 then file_path
Else @ master_path
End
When 'l' then case @ flag_file
When 1 then file_path
Else @ master_path
End
End
+ Case type
When 'd 'then @ restore_db_name
+ '_ Data'
/** // * Number the object */
+ Convert (sysname, IDS)
+ '.'
/** // * Add a suffix to the file, MDF or NDF */
+ Right (physicalname, 3)
+ ''','
When 'l' then @ restore_db_name
+ '_ Log'
/** // * Number the object */
+ Convert (sysname, IDS)
+ '.'
/** // * Add a suffix to the file, MDF or NDF */
+ Right (physicalname, 3)
+ ''','
End
From @ TEM
Select @ SQL = 'Restore Database @ db_name'
+ 'From disk = @ filename'
Select @ SQL = @ SQL + @ SQL _sub + 'replace'
Select @ par = '@ db_name nvarchar (128), @ filename nvarchar (260 )'
/** // * Close the related process and import the corresponding process status to the temporary table */
Select Identity (INT, 1, 1) IDs, spid
Into # temp
From master .. sysprocesses
Where dbid = db_id (@ restore_db_name)
/** // * Find the corresponding process */
If @ rowcount> 0
Begin
Select @ max_ids = max (IDS)
From # temp
Select @ loop_time = 1
While @ loop_time <= @ max_ids
Begin
Select @ SQL _kill = 'Kill '+ convert (nvarchar (20), spid)
From # temp
Where IDs = @ loop_time
Execute sp_executesql @ SQL _kill
Select @ loop_time = @ loop_time + 1
End
End
Drop table # temp
Execute sp_executesql @ SQL,
@ Par,
@ Db_name = @ restore_db_name,
@ Filename = @ filename
/** // * Operation succeeded */
Select @ flag = 'OK'
End
Else
Begin
/** // * Parameter @ filename input format Error */
Select @ flag = 'file type error'
End

Go

 

/* Run */

-- Backup database test_database
Declare @ FL varchar (10)
Execute pr_backup_db @ FL out, 'test _ database', 'c:/test_database.bak'
Select @ fl

-- Restore the database. The input parameter is incorrect.
Declare @ FL varchar (20)
Exec pr_restore_db @ FL out, 'sa ', 'c :/'
Select @ fl

-- Restore the database, that is, create a copy of the database test_database test_db
Declare @ FL varchar (20)
Exec pr_restore_db @ FL out, 'test _ db', 'c:/test_database.bak'
Select @ fl