server| Backup | recovery | data | Database Summary backup and recovery is an important operation for database administrators to maintain database security and integrity. This article analyzes and introduces the method of backing up SQL database with access, and achieves the purpose of data backup well. At the same time, the security interface of Access database is discussed briefly, so that the data security is not lost after the backup. Some suggestions for improving backup and recovery strategy are proposed.
Introduction
For critical applications of the database system should be based on specific circumstances and conditions to develop a sound and feasible to ensure system security of the backup plan, no backup system is unreliable, dangerous, the consequences will be serious, destructive viruses, misoperation, natural disasters and so on may cause incalculable damage to the database system. Especially for a higher level of information enterprises, the most valuable property may be the data in the enterprise database. The most important thing in a system is a lot of real-time data, and no backup function is obviously unsafe and imperfect. After working out the backup program developed by Visual Basic 6.0, you can back up the database to an Access database (access is the current popular desktop database product with the advantages of flexibility and convenience in storage operations, and because it is a Microsoft database product with SQL Server, Both are well compatible) and encrypt Access databases to prevent data from being illegally accessed. The program also restores the SQL Server database with data from the Access backup library, thereby reducing the damage caused by the misuse of corrupted data content. Using this method, users do not need to add additional equipment, only to run the backup program on the original server, its interface is simple and convenient, suitable for ordinary users.
Overview of Backup and Recovery
A backup is the process by which a database administrator periodically copies the entire database to a tape or another disk. Common types of data backups include full backups, differential backups, transaction log backups, file or filegroup backups. A common backup strategy is to perform a full backup at a long interval, differential backups at moderate intervals, and transaction log backups at short intervals. When data is compromised, backups can be used to recover the database. The recovery database is a backup that mounts the database, and then the process of applying the transaction log rebuild. A common recovery strategy is to recover fully backed-up data first, then restore differential backup data, and finally recover from transaction log backups.
1. Backup plan
Develop the software you need: Visual Basic 6.0 Access Backup program design methods:
(1) Add a data source name to the database in SQL Server using the ODBC settings of the control surface version.
(2) Create a new database in Access named Backup.mdb.
(3) Use VB to define two main processes: Attach_table () and create_table (). The role of attach_table is to include all tables in the SQL Server database that need to be backed up in the Access database, in which case the system table is excluded because it was created by SQL Server and has no user data in the table. The role of create_table is to create the destination table in Backup.mdb, in which the table is created to hold all user data that exists in the SQL database. Also, in the link process, remove the owner name from SQL Server.
2, the logical structure of the program
① Open the SQL database that you want to back up by using the ODBC data source you created in the first step;
② uses attach_table () to link all user tables in the database to Backup.mdb, and the method for excluding the system table is:
For each TDF in backup_db. TableDefs
If (Tdf.attributes and Dbsystemobject) =0 Then
The condition is set, indicating that the table is a user-defined table that can be linked or skipped
End If
Next
③ use Create_table () to create a corresponding backup table for each table, the name can be followed by the following principles, packages table called TABLE_NAME, the backup table is called B_table_name.
④ copies all the records in the TABLE_NAME table to B_table_name.
⑤ deletes a link to the SQL data table from the backup library.
3, the program original code
Using the Microsoft DAO (data Access Object) data model, open the Access local database and connect an external ODBC datasheet, copy the table structure complete, and have the following procedure block Copystru:
Private Sub Copystru ()
Set dbstemp = Wrkjet. OpenDatabase (tagfilname)//Link table process
For i = 0 to TabN-1
Set tdflinked = dbstemp. CreateTableDef ("Linktab")
Tdflinked. Connect = "OdbC;" DATABASE = Xgsbgsys; UID =
SA; PWD =; DSN = Xgsdb; "
Tdflinked. SourceTableName = TabName (i)
Dbstemp. TableDefs. Append tdflinked
Set temp Tab = dbstemp. CreateTableDef ()
Temp Tab. Name = TabName (i)
The process of creating a new table
For each fld in tdflinked. Fields
Set Newfil = temp Tab. CreateField (fld. Name, fld. Type, fld. Size)
Newfil. OrdinalPosition = fld. OrdinalPosition
Newfil. Required = fld. Required
Temp Tab. Fields. Append Newfil
Next
Create an index
For each idx in tdflinked. Indexes
Set newidx = temp Tab. CreateIndex ()
With Newidx
Name = TabName (i) & "X"
Fields = idx. Fields
Unique = idx. Unique
Primary = idx. Primary
End With
Temp Tab. Indexes. Append Newidx
Next
Dbstemp. TableDefs. Append Temp Tab
Set Temp Tab = Nothing
Dbstemp. TableDefs. Delete "Linktab"
Next I
Dbstemp. Close
Set dbstemp = Nothing
Wrkjet. Close
Set Wrkjet = Nothing
End Sub
End Sub
[1] [2] Next page
