How to automatically back up and implement Incremental backup for database changes? Looking for DTS information?

With the increasing degree of software automation, database maintenance is a serious problem found in software development. A recent project found that some tables have lost primary keys, some handwritten stored procedures are rewritten as automatically generated by tools.
How to automatically back up and implement Incremental backup for database changes? Looking for DTS information?
Regular backup: however, it cannot be saved as an SQL statement and cannot be compared. The following information is provided:

(Source: CCID technical network)

Step 1: Open the SQL Server "Enterprise Manager" Form

Click Start in the taskbarProgram(P) In the "Microsoft SQL Server" sub-menu under the "Enterprise Manager" menu, you can open the "Enterprise Manager" form of SQL Server 2000.

Step 2: Find the "Database Maintenance Plan" Function

On the tree tab on the left of the Enterprise Manager form, click the + icon to expand Microsoft SQL servers under the root directory of the console ", you can see that there is an "SQL Server group" under it, and then expand the "SQL Server group ", now you can see the name of the server under it (the "JXNC-SERVER" in Figure 1 is the name of my server); continue to expand this server, you can see that projects such as "Database" and "Data Conversion service" are listed. Click "manage" to view a "database maintenance plan" (1 ).

Figure 1

Click "Database Maintenance Plan". An existing maintenance plan project is displayed on the right side of the "Enterprise Manager" window. Each maintenance plan includes the following items:

1. Name: the name of the maintenance plan. This name can be customized in both Chinese and English.

2. Database: the name of the database maintained in the maintenance plan.

Because a maintenance plan allows simultaneous maintenance of multiple databases, therefore, the names of multiple databases can be displayed here (in Figure 1, we can see that the database maintenance plan named "System Database Backup" contains three databases: master, model, and MSDB ).

3. SERVER: the name of the server where the database maintained by the maintenance plan is located. "(Local)" indicates a local server.

4. Countermeasure: it refers to the specific maintenance work required by the maintenance plan.

In Figure 1, three "database maintenance plans" are "database backup, transaction log backup ", it means that the specified database is backed up by both "Database" and "transaction log" in these maintenance plans.

Step 3: Create a database maintenance plan"

Right-click the "Database Maintenance Plan" project and select the "new maintenance plan (p)" function to open the "database maintenance plan wizard" form, follow this wizard to create a new "Database Maintenance Plan ".

Step 1: click "next (n)" to open the "select database" form (2 ). In this form, you can select one or more databases as operation objects. For ease of description, I have selected only one database "regie" here ".

Figure 2

Step 2: click "next (n)" In Figure 2 to open the "Update Data Optimization information" form (3 ).

Figure 3

In this form, you can re-organize the data and indexes in the database, and set the maintenance plan to automatically delete unused space in the database when certain conditions are met, so as to improve performance.

Note that in this form, you only need to select the "reorganizing data and index page [R]" check box and "update statistics used by the query optimizer. The check box for example [d] will become invalid (it turns gray and cannot be selected ). In addition, either the "re-organize data and index page [R]" check box or the "delete unused space [m] from database files" check box is selected, the "scheduling [s]" function under it is valid. Click "change [c]" to customize "scheduling.

Readers can decide whether to use the functions based on their own situations. You can also click the "help" button to view the specific meanings of each function.

In this form, you can easily set the running duration and frequency of each job. After completing the settings, You must select the "enable scheduling [B]" check box in the upper-right corner to ensure that a job scheduling is completed.

Step 3: click "next (n)" in Figure 3 to open the "check database integrity" form.

In this form, you can set the maintenance plan to automatically check the database integrity before backing up the database to detect data inconsistency caused by hardware or software errors. In this form, only the check box "Check database integrity [H]" is selected, and the "perform these checks before backup" and "schedule [s]" functions under it are valid. Click "change [c]" to customize "scheduling.

Readers can decide on their own. A better way is to select the "check database integrity [H]" check box (recommended because some errors may be corrected ).

Step 4: In the "check database integrity" form, click "next (n)" to open the "specify database backup plan" form.

To back up a database, You must select the "Back up database [a] as part of the maintenance plan" check box and specify the location where the backup files are stored: tape [p] or disk [K].

If "disk [k]" is selected as the database backup location, set "scheduling" and click "next (n)", the "specify backup disk directory" form (4) is displayed ).

Figure 4

In Figure 4, you can specify the directory for storing backup files (you can use the default directory or customize it) and the backup file extension, it can also indicate that the backup plan is automatically deleted earlier than a certain time period (2 days is set in figure 4, that is, all the backup files created two days ago will be automatically deleted, only the backup files of the last two days are left. The "create sub-directory [c] for each database" function in Figure 4 is only useful when multiple databases are selected in step 1. It does not play a major role in one database. After setting, click "next (n)" to display the "specify transaction log backup plan" form.

If you select "tape [p]" as the database backup location, set "scheduling" and click "next (n)", the "specified transaction log backup plan" form is displayed directly.

Step 5: The process of specifying the "transaction log backup plan" is exactly the same as that of Step 4, but it is slightly different in setting "scheduling" (because my requirement is that the database is backed up once a day, transaction logs are backed up once every hour ).

Step 6: After all the transaction log backup plans are set, click "next (n)" to display the "Report to be generated" form.

In this form, you can specify a directory for storing logs during the execution of the backup plan. The setting process is similar to the operation in Figure 4.

Figure 5

Step 7: After step 6 is completed, click "next (n)" to display the "maintenance plan history" form.

How to automatically back up and implement Incremental backup for database changes? Looking for DTS information?