Tape backup and recovery of data in MS SQL Server under Web environment

server|web| Backup | recovery | data

Absrtact: This paper introduces the working process of tape data backup and recovery, including a database with a capacity equivalent to tape on the hard disk, that is, bridge database
And in the Web Information system to achieve a full backup of tape data and restore functions. This paper expounds how to use the existing database backup and restore in SQL from the theory and practice.
Complex commands and the IDC technology in NT.
Keywords: Web information system; Tape data backup; Bridge database; IDC file; Data recovery

1 Introduction

The ability to implement tape backup data is difficult in two ways: first, the overall backup and recovery of the database provided by MS SQL Server (hereinafter referred to as SQL)
Features do not directly meet the system requirements of the data rolling backup. 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 system
The realization of the function can ensure that the data is always in the database, and the system administrator can restore the backup data at any time for users to query. This article is the real
The present function has universal meaning, especially suitable for small and medium-sized enterprises to develop the management information system based on intranet technology.

2 process of backup and recovery of tape data

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
The capacity of the tape is comparable to the database we call the bridge database, such as FJJDBBK. It is easy to implement a scrolling backup of the data. Its working process is: the administrator put
A month's data is moved from the primary database to the bridge database, and the bridge database is backed up to tape using the SQL Backup feature. Users need to view historical data in tapes
, the system restores the data in the tape to the bridge database with SQL Recovery backup function, and the user looks up the historical data directly in the bridge database. In addition, in the data
After backing up to tape, the data in the backup database must be emptied before the next data backup. In order to improve efficiency, we have established a blank bridge data
Library (the bridge database when there is no data). This blank database is used to recover the bridge database before each tape backup data is performed. When you create a blank database file
Keep well and not be 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. Establish
The best way to do these table structures is to use the Generate SQL scripts function under the Enterprise Manager's Object menu to build the tables
Scripts file, then select the FJJDBBK database and run the file using the Query Analyzer feature under Enterprise Manager's Tools menu to establish
The required tables. 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.

Implementation of backup and recovery of 3 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 kinds of text
Items: 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 with the "+" number
Begins as a token to differentiate each SQL statement. It should be emphasized that for MS SQL databases, IDC can use any of its standard commands and functions. For example, define
variables, control data flow, etc. (see example).
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 to the IDC interface module, IDC will read and interpret the contents of IDC files, through the ODBC module to the database server to send service requests; The database server will
The execution results are returned to the IDC module via an ODBC interface. Then the IDC module inserts the result into the specified template file and forms an actual HTML file to the IIS;
The HTML is then returned to the browser by IIS.
In the Web information system to achieve complete tape data backup and recovery functions need to implement the following three functions: "Data Backup" function, "Data recovery" function
And the "Clean data" feature. The data backup feature ensures that the system administrator operates on the basis of the starting and ending days of the data that the user enters to back up
Period, the data to be backed up is transferred from the primary database to the bridge database, and then the function is implemented using the SQL DUMP command. The "Data recovery" function is to ensure that the system tube
The function of "Restoring Bridge Database" and "Restoring Blank Bridge Database" is provided under the premise of the agent operation. This function can be achieved by using the SQL Load command;
, "Clean up the data" function also to ensure that the system administrator operation, based on the user entered the data to be deleted from the start date and end date, the use of SQL
Deletes the data in the current database from the Delete command in the So. For the smooth implementation of the data backup feature and the Clean Data feature, each table is required to be archived
The Time field.
The implementation of the system tape data backup and recovery of some of the IDC files are as follows:
/*bkup. idc*/
Datasource:web SQL
Template:BKUP.htx
Username:sa
Password:sa
SQLStatement:
+if%jb%=1
+/* Judge System Maintenance Officer * *
+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 */
+select The 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 database 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. Then delete the first 4th
Months of data. The realization of the current data and historical data synchronization review, which is a significant advantage of the web system.

4 Conclusion

This paper introduces a special method of data tape backup and recovery. It should be noted that the database backup operation is the database management system is very
Important link, and the database backup operation process is more complex, therefore, we must establish a sound rules and regulations, by a professional system administrator to do several
According to the database backup and restore operation.