Tape backup and recovery of data in Web environment

web| Backup | recovery | data
The ability to implement tape backup data is difficult in two ways: first, the overall backup and recovery capabilities of the database provided by MS SQL Server do not directly meet the data rolling backups required by this system. Second, you need to address how to implement tape data backup in a Web environment.

With the existing database backup and restore commands in SQL and the IDC technology in NT, the function of data rolling back to tape in SQL database is realized. The function of the system can guarantee that the data is always in the database, and the system administrator can restore the backup data at any time for the user to inquire. The functions implemented in this paper are of universal significance, especially suitable for small and medium-sized enterprises to develop the management information system based on intranet technology.

The process of backup and recovery of tape data

In order to take full advantage of the existing database backup and recovery capabilities in SQL to reduce the cost of tape data backup, we specifically built a database with a capacity equivalent to tape on the hard drive, which we call the bridge database, such as FJJDBBK. It is easy to implement a scrolling backup of the data. The process is that the administrator moves a month's data from the primary database to the bridge database, and then backs up the bridge database to tape using the SQL Backup feature. When a user needs to view historical data in a tape, the system restores the data in the tape to the bridge database using the SQL Restore Backup function, and the user searches the historical data directly in the bridge database. In addition, after the data is backed up to tape, the data in the backup database must be emptied before the next data backup. To improve productivity, we built a blank bridge database (the bridge database when there was no data). This blank database is used to recover the bridge database before each tape backup data is performed. After the establishment of the blank database file to save, not arbitrarily deleted.

The process of establishing the backup blank database is to first establish the primary database FJJDBBK and then create a table in FJJDBBK that is the same structure as the current database. The best way to build these table structures is to use the Generate SQL scripts function under the Enterprise Manager's Object menu to generate scripts files that create these tables, and then select FJJDBBK Database to use Enterprise The Query Analyzer function under the manager's Tools menu runs the file, and the required tables are established. Obviously, the FJJDBBK is an empty database. We then use the Backup command or the Backup/restore feature under the Run Tools menu to back up the empty database.

The implementation of backup and recovery of tape data
In NT, Web server IIS (Internet Information Server) provides a sophisticated technology IDC for accessing SQL. IDC is a DLL file (HTTPODBC.DLL), in fact, it can access various databases through the ODBC interface. When you implement a Web page to access a database, you need to establish two types of files: IDC file (*.IDC) and HTML template file (*.HTX). The IDC file is used to control access to the database. It is generally made up of two parts:
The file header indicates the system data source, template file name, username, and password. Sometimes it also includes parameter settings for retrieving the database.
This part of the file body is marked with "SQLStatement:" and writes the standard SQL statement that manipulates the database. Note that each statement starts with a "+" sign, which is used as a marker for distinguishing each SQL statement. It should be emphasized that for MS SQL databases, IDC can use any of its standard commands and functions. such as defining variables, controlling data flow, and so on.

The process of IDC is as follows: First, the Web server IIS analyzes the URL string that came from the browser. If the current string ends with "*.IDC", the IDC request is forwarded to the IDC interface module, and IDC reads and interprets the contents of the IDC file, sending service requests to the database server through an ODBC module; The database server returns the results of the execution to the IDC module via an ODBC interface. Then the IDC module inserts the result into the specified template file, forms an actual HTML file to the IIS, and finally the HTML is returned to the browser by IIS.

To achieve full backup and recovery of tape data in a Web information system requires the following three features: data backup, data recovery, and clean data. The data backup function, in keeping with the system administrator's operation, is to transfer the data to be backed up from the main database to the bridge database according to the starting date and the expiration date of the user input data to be backed up, and then implement the function by using the SQL Dump command. The data recovery feature provides the "Recover Bridge Database" and "restore the Blank Bridge database" in the context of ensuring that the system administrator operates. As long as the use of SQL Load command to achieve these functions; Similarly, the "Clean data" feature ensures that the data in the current database is deleted using the delete command in SQL based on the starting and ending dates of the data that the user entered to delete, on the basis of the system administrator's actions. So. For the smooth implementation of the data backup feature and the Clean Data feature, each table is required to have an archive time field.
The partial IDC file that implements the backup and restore function of the tape data of this system is as follows:
/*bkup. idc*/
Datasource:web SQL
Template:BKUP.htx
Username:sa
Password:sa
SQLStatement:
+if%jb%=1
+/* Judges System maintainers */
+select qsrq=convert (varchar (), GETDATE (), 1),
Zzrq=convert (varchar (), +dateadd (day,1, GETDATE ()), 1
+/* preset start and end dates */

/*bkupcz. idc*/
Datasource:web SQL
Template:BKUPCZ.htx
Username:sa
Password:sa
SQLStatement:
+insert FJJDBBK. Dbo. Jkld SELECT * from Fjjdb. Dbo. JKLD
+where CONVERT (varchar (), gdsj,1) >=′%qsrq%′
and CONVERT (varchar (), gdsj,1) < =′%zzrq%′
...
+/* the selected data in the current database into the bridge database */
+dump database fjjdbbk to TAPE =′\\.\tape0′with INIT, nounload
+/* Backup Data */
+ The SELECT ts=′ Backup database operation has completed!′
/*huif. idc*/
Datasource:web SQL
Template:HUIF.htx
Username:sa
Password:sa
SQLStatement:
+if%jb%=1
+begin
+load database fjjdbbk from Tape=′\\.\tape0′with
nounload
+/* Restore Backup Data */
+select ts=′ restore operation completed !′+/*
+end

/*qlsjkcz. idc*/
Datasource:web SQL
Username:sa
Password:sa
Template:QLSJKCZ.htx
SQLStatement:
+delete Fjjdb. Dbo. Jkld
where CONVERT (varchar (), gdsj,1) >=′%qsrq%′and
CONVERT (varchar (), gdsj,1) <=′%zzrq%′
............
+/* Delete the selected data in the current database * *
+select ts=′ Delete operation completed!′
In this system, the main database is guaranteed to store 4 months of data. System requires that the system administrator back up the data to tape last month at the beginning of each month. The data for the previous 4th month is then deleted. The realization of the current data and historical data synchronization review, which is a significant advantage of the web system