SQL Server data backup and recovery in Web Environment

1. Introduction

There are two difficulties in realizing the function of tape backup data: first, ms SQL Server (SQL) the overall backup and recovery functions of the provided database cannot directly meet the requirements of the system for data rolling backup. Second, we need to solve how to implement the tape data backup function in the Web environment.
The SQL database backup and recovery commands and the IDC technology in NT are used to implement the data rolling backup to tape function in the SQL database. The functions implemented by the system not only ensure that the data in the database is always in the recent period of time, but also ensure that the system administrator can restore the backup data at any time for users to query. The functions implemented in this article have universal significance and are especially suitable for small and medium-sized enterprises to develop management information systems based on Intranet technology.

2. Tape data backup and recovery

In order to make full use of the existing database backup and recovery functions in SQL to reduce the cost of tape data backup, we have built a database on the hard disk that is equivalent to the tape capacity, we call it a bridge database, such as fjdbbk. You can easily implement rolling backup of data. The working process is: the Administrator moves the data of a month from the master database to the bridge database, and then uses the SQL backup function to back up the bridge database to the tape. When you need to view the historical data in the tape, the system uses the SQL restoration and backup function to restore the data in the tape to the bridge database. You can directly view the historical data in the bridge database. In addition, after the data is backed up to tape, the data in the backup database must be cleared before the next data backup. To improve work efficiency, we have established a bridge database when there is no data in the blank bridge database ). Use this blank database to restore the bridge database before each tape backup data. The blank database files must be saved after they are created and cannot be deleted.
The process of creating the blank backup database is: first create the primary database FJJDBBK, and then create a table with the same structure as the current database in FJJDBBK. The best way to create these table structures is to use the Generate SQL Scripts function under the Object menu of Enterprise Manager to Generate the Scripts file for these tables, and then select the FJJDBBK database, run the file using the Query Analyzer function in the Tools menu of Enterprise Manager, and create these required tables. Obviously, FJJDBBK is an empty database. We use the Backup command or the Backup/Restore function under the Tools menu to back up the empty database.

3. Tape data backup and recovery implementation

In NT, the Web Server IISInternet Information Server provides a comprehensive technical IDC for accessing SQL. IDC is a DLL file HTTPODBC. DLL). In fact, it can access various databases through the ODBC interface. To access the database on a Web page, you must create two types of files: IDC File *. idc) and HTML template file *. htx ). The IDC File is used to control database access. It generally consists of two parts:

◆ File Header
Specifies the system data source, template file name, user name, and password. Some parameter settings related to database retrieval are also included.

◆ File subject
This part starts with "SQLStatement:" and then writes standard SQL statements that manipulate the database. Note that each statement starts with "+" and serves as a marker for distinguishing each SQL statement. It should be emphasized that for ms SQL databases, IDCs can use any of their standard commands and functions. For example, to define variables and control data streams, see instances ).
The process of IDC processing is as follows: first, the Web server IIS analyzes the URL string sent from the browser. If the current string is at the end of "idc", the IDC request is sent to the IDC interface module. The IDC reads and interprets the content of the IDC File in sequence and sends a service request to the database server through the ODBC module; the database server returns the execution result to the IDC module through the ODBC interface. Then, the IDC module inserts the result into the specified template file to generate an actual HTML file and hand it over to IIS. Finally, IIS returns the HTML file to the browser.
To implement complete tape data backup and recovery functions in the Web Information System, you must implement the following three functions: "data backup", "data recovery", and "data cleanup. The "data backup" function ensures that the system administrator can perform operations based on the start date and end date of the data to be backed up, migrate the data to be backed up from the primary database to the bridge database, and then use the SQL DUMP command to implement this function. The "data recovery" function provides the "Recover bridge Database" and "Restore blank bridge Database" functions while ensuring system administrator operations. You only need to use the LOAD command of SQL to implement the above functions. Similarly, the "clean up data" function must be performed by the system administrator, use the DELETE command in SQL to DELETE the data in the current database based on the start and end dates of the data you enter. Therefore. For the smooth implementation of the "data backup" and "data cleanup" functions, each table must have an archive time field.

Some IDC files that implement the tape data backup and recovery functions of the system are as follows:

/* BKUP. IDC */ Datasource: Web SQL Template: BKUP. htx Username: sa Password: sa SQLStatement: + If % jb % = 1 +/* Judge the system maintainer */ + SELECT qsrq = convert (varchar (12), getdate (), 1 ), Zzrq = convert (varchar (12), + dateadd (day, 1, getdate (), 1) +/* Preset start date and end date */ /* 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 (12), gdsj, 1)> = '% QSRQ % ′ And convert (varchar (12), gdsj, 1) <= '% ZZRQ % ′ ............ +/* Transfer the selected data in the current database to the bridge database */ + Dump database fjjdbbk to tape = '\. tape0' with init, NOUNLOAD +/* Back up data */ + Select ts = 'backup database operation completed! ′ /* HUIF. IDC */ Datasource: Web SQL Template: HUIF. htx Username: sa Password: sa SQLStatement: + If % jb % = 1 + Begin + Load database fjjdbbk from tape = '\. TAPE0' NOUNLOAD +/* Recover backup data */ + Select ts = 'the database Restoration Operation has been completed! '+ /* + End /* QLSJKCZ. IDC */ Datasource: Web SQL Username: sa Password: sa Template: QLSJKCZ. htx SQLStatement: + Delete FJJDB. DBO. JKLD Where convert (varchar (12), gdsj, 1)> = '% QSRQ %' and Convert (varchar (12), gdsj, 1) <= '% ZZRQ % ′ ............ +/* Delete selected data in the current database */ + Select ts = 'delete operation completed! ′

In this system, the primary database is guaranteed to store data for four months. The system requires that the system administrator Back up data from the previous month to tape at the beginning of each month. Delete the data from the first 4th months. It achieves synchronous query of current data and historical data, which is a significant advantage of the Web system.

4. Conclusion

This document describes how to back up and restore Data tapes. It should be pointed out that the database backup operation is a very important part of the database management system, and the database backup operation process is complicated. Therefore, we must establish sound rules and regulations, professional System Administrators can back up and restore databases.