Explanation of SQL Server "Database Maintenance Plan"

As a network manager, no matter whether the size of the managed network is large or small, in addition to maintaining the stable operation of the network, eliminating network faults in a timely manner, and protecting network security, key data in the backup network is also a very important task. There are only two types of faults in the Network: software faults and hardware faults. "Hardware faults" can be solved through repair or replacement of hardware equipment in a timely manner; "software faults" can be solved in a timely manner by re-installing or upgrading the software, redoing the network, or applying the software system, in addition, most of the solutions to network faults require some basic and critical data support to restore the normal operation. However, the vast majority of such critical data in the network (especially the key data in the "application software system") cannot be recovered or compensated. Even if some data can be recovered, making up for the cost (such as time, manpower, financial resources, and material resources) may be far beyond the company's affordability.

Therefore, data backup is a task that the network administrator must always pay attention to in daily management and must be operated repeatedly periodically.

Directory

Current backup policy
Implementation steps
Conclusion

Current backup policy

When we set up a local area network and considered the characteristics of commercial enterprises, after carefully considering the data and storage problems that occurred in the three phases of purchase, sale and storage, select a software platform that uses Windows 2000 Server as the operating system and SQL Server 2000 as the database platform to build a LAN application system, using the network cable as the carrier, computers in core departments such as purchase, sales, and storage are closely connected through the LAN platform. In this way, every core Department's daily business is stored in the SQL Server 2000 database on the core Dell server of the company's LAN in a timely and dynamic manner, the platform provides various data services required by all parties.

Therefore, as a network manager, I have been paying great attention to the backup of key data in the LAN, especially the business data, since the start of the company's local area network. At the same time, I also hope that the software that backs up data can implement the following automatic functions.

Function 1: it can be set at a fixed time every day (for example, at 0:00:00, of course, this time can be set independently) perform a "full backup" for all key databases, including all business databases ".

Function 2: during the period from 0:00:00 to 23:59:59 every day, the transaction logs of each database involved in function 1 can be "differential backup" every one hour ".

Feature 3: the database and transaction logs generated in feature 1 and Feature 2 can be retained every day (that is, the backups of the previous day and the previous two days ), it can also automatically delete backups of all databases and transaction logs two days ago.

Function 4: regularly back up copies of all critical databases to tape or other storage media (this may need to be done manually ).

As a result, I tried every possible software and methods to back up data in my daily management work, for example, the "backup" tool provided by Windows 2000 and the "backup" function provided by SQL Server 2000. These backup software and methods have their own functions, but they all have the following Disadvantages:

Manual intervention is required to achieve automatic backup (for example, the "backup" tool provided by Windows 2000 ). If this method is used, manual backup is required at all times. In case of another server or data failure due to a business trip or other reasons, the trouble will be high.

The other is automatic backup, but the old backup cannot be automatically deleted (for example, the "backup" function provided by SQL Server 2000 ). If this method is used, you must manually delete the old backup in time. Otherwise, the large hard disk will be quickly used up.

After comparison, I decided to back up key databases using the "backup" feature provided by SQL Server 2000, because it can implement the "Automatic Backup" function, it is slightly better than the first one. Therefore, during a period of time, the first thing I do after work every day is to first check the new backup of various data under the Backup Directory, and then manually delete the old backup data. At one time, I was very upset about this practice.

One day, I accidentally read "automated management tasks" when I used SQL Server 2000's "help" to query the semantic interpretation of a Transact-SQL statement. After reading it carefully from start to end, I couldn't help but have a bright eye. Originally, SQL Server 2000 itself had a powerful feature that could meet my backup requirements-"Database Maintenance Plan ". As a result, I immediately followed the prompts and took a database as a test sample to operate step by step, successfully creating a database maintenance plan. After one week of trial run, this plan can achieve automatic backup scheduling and automatic deletion of old data backups, fully meeting my backup requirements.

Since then, I have used the "Database Maintenance Plan" of SQL Server 2000 to back up all key databases and strictly and regularly executed function 4, every Friday, a fully backed up database is backed up to the hard disk of the tape and other clients in the LAN (primarily the network management PC for network management. The purpose is to save three identical backups in different regions at the same time to reduce the loss caused by faults.

In addition, through the "Database Maintenance Plan" of SQL Server 2000, I am now able to easily back up all kinds of data needed, facilitate management of the backup, and correspondingly reduce the daily workload, it also reduces some work pressure.

Implementation steps

Directory

Step 1: Open the SQL Server "Enterprise Manager" Form
Step 2: Find the "Database Maintenance Plan" Function
Step 3: Create a database maintenance plan"
Step 4: maintain and manage the "Database Maintenance Plan"
Step 5: Start the SQL Server 2000 proxy to execute the "job"
Step 6: Check Results

The "Database Maintenance Plan" function can be found in "Enterprise Manager" of SQL Server 2000.

Note:

1. The following operations are performed on the Windows 2000 Server. The operations on Windows 9x are the same.

2. Since SQL Server 2000 generates many files during Backup (especially when backing up transaction logs), we recommend that you create separate backup directories based on the database name for storage.

3. The following operations generally do not affect the use of the database.

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.

In this form, you can specify how to store the historical records of this maintenance plan (whether it is stored on the "local server" or "remote server ), you can specify the number of rows in a table to limit the storage size of historical records.

Step 8: After step 7 is completed, click Next (n) to display the "completing database maintenance plan wizard" form (5 ).

In this form, you can customize a "plan name [p]" (recommended for ease of management and identification). Of course, you can also use the default "plan name [p]". You can also confirm the content in the text box "plan name [p, you can click the "previous [B]" button in the form to return to the corresponding form for modification.

Step 9: After Step 8 is completed, click the "finish" button to display "the maintenance plan has been created successfully ." And then click "OK" to create a new database maintenance plan.

As shown in figure 6, a new database maintenance plan-"regie backup" has been created successfully ".

Step 4: maintain and manage the "Database Maintenance Plan"

After step 3 is completed, the daily maintenance and management of each "Database Maintenance Plan" are very convenient, you only need to double-click "Database Maintenance Plan" to modify and modify the content involved in step 3.

Figure 6

6. Right-click "regie backup", click "properties [R]", or double-click "regie backup" to open the "Database Maintenance Plan" form. All the functions involved in step 3 are integrated in this form. Each function can be modified at will. The modification process is the same as that in step 3.

However, when you set the "delete text report file [f] earlier than this time in" text report "under" reports "under" reports "in figure 7, that is, the content in step 6, whether you set it to "Minute", "Hour", "day", or "month ", after the creation is successful, it will be automatically corrected as "Week", and it will be displayed as "Week" no matter how it is modified in the future ", but it does not mean that other options are invalid. Other options are still valid.

Figure 7

Step 5: Start the SQL Server 2000 proxy to execute the "job"

After completing step 3, you also need to start the SQL Server 2000 agent to execute the database maintenance plan job.

As with the SQL Server 2000 "Database Maintenance Plan", you can find an "SQL Server proxy" (8) in the "manage" project ).

Figure 8

Click the "job" sub-menu under "SQL Server proxy, an existing job project is displayed on the right side of the Enterprise Manager window (17 job projects are displayed in Figure 8 ). Each job project includes the following data columns:

1. Name: Of course, it refers to the job name, which can be customized and can be used in both Chinese and English. For ease of understanding, we recommend that you use both Chinese and English.

Each time a "Database Maintenance Plan" is created, jobs with the following default names are automatically generated:

(1) When the "backup database" function is set in the new "Database Maintenance Plan, the default database backup job named "DB maintenance plan" is generated.

(2) when the "backup transaction log" function is set in the new "Database Maintenance Plan", a transaction log backup job named "DB maintenance plan" ******* is generated by default (multiple servers).

Above (1) and (2) the "Plan Name" in "Database Maintenance Plan" is displayed in "*****" (that is, the "Plan Name" set in step 3 8 ").

2. Category: Specify the current category of the job. The default value is [unclassified (local)].

3. Enable: indicates whether the job is "enabled.

4. runable: indicates whether the job is in the "runable" status.

5. Scheduled: indicates whether the job is in the "scheduled" status.

6. Status: indicates the current running status of the job-not running or running.

7. last running status (start date): displays the status ("succeeded", "failed", or "unknown") after the last running of the job, and the date and time of the run.

8. Next run date: specify the date and time of the next run of the job.

8. Right-click the "regie full backup" job, click "properties [R]", or double-click the "regie full backup" job to open the "properties" form of the job. The detailed configuration items of the job are integrated in this form. Each configuration item can be modified at will. The "properties" form has four tabs:

◆ General: On this tab, You can reset the job name ("name [N]" text box) and modify the job category ("category [y]" drop-down box), specify the job owner ("owner [w]" drop-down box), and briefly describe the job ("Description [R]" text box ), and decide whether to enable this job (the "enable [e]" check box ).

◆ Step: On this tab, you can create and insert new steps to delete and edit existing steps.

Click the edit [e] button to view the execution statement of the job in the "command [m]" text box on the "General" tab of the "edit job step" form.

◆ Scheduling: On this tab, you can create scheduling, alarm creation, delete, and edit existing scheduling.

◆ Notification: On this tab, you can set the operations performed when the job is completed (that is, when the job is successful or fails, this means sending emails, calling operators, sending network alerts, writing Windows Application Event Logs, and automatic deletion.

Step 6: Check Results

After the above five steps, a complete database backup plan is established. You can use "Resource Manager" to check whether the backup file exists in the backup directory.

After a long period of use, if a complete database maintenance plan is correctly established using the backup policy of "retaining 2-day full database backup and 2-day transaction log backup every hour, whenever you view the files in the corresponding backup directory, there should be 102 files:

◆ Complete backup of two databases, that is, two files named in the format of "database name_db_yyyy mmddhhss. Bak;

◆ 3 Record Files corresponding to the operation process of the data warehouse, that is, files with file names in the format of "444_yyyymmddhhss.txt;

◆ Differential backup of 48 transaction logs, that is, 24 files each in two days in the format of "database name_tlog_yyyymmddhhss.trn;

◆ Objects "is a file name.

In the above file name, "Database Name" is the name of the selected database in step 3; "yyyymmddhhss" is the timestamp, in the format: "YYYY" refers to "year" (4-digit value), "mm" refers to "month" (2-digit value, less than 2-digit fill "0 "), "DD" refers to "day" (two-digit value, less than two-digit complement "0"), "hh" refers to "Hour" (two-digit value, if there are less than two digits, fill "0"), and "SS" indicates "Minute" (two digits, less than two digits, fill "0 ").

Conclusion

For an enterprise, all the data generated by various businesses that occur in daily operation is accumulated over time by computer, and gradually becomes a kind of wealth and capital of the company. By using computers, you can conveniently collect and analyze some or all of the data, and provide various forms of feedback (tables, tables, etc.) to the company's decision-making layer for reference, it is easy to provide guidance and help for future decisions of the company. Based on this, the value of these data is growing at a geometric speed over time. Therefore, I believe that data backup is one of the most important tasks in the daily work of network administrators.

Through the creation of the "Database Maintenance Plan", I felt a little bit:

1. after such a long period of use, I think there are still some shortcomings in the "Database Maintenance Plan". Although the "Database Maintenance Plan" is powerful, its final result is to generate a job, the "SQL Server 2000 agent" Service regularly performs database backup. This requires the "SQL Server 2000 agent" service to "run" normally ". From multiple installations, the "SQL Server 2000 agent" service can run properly in Windows 2000 and automatically run with Windows 2000. However, Windows 98 (including the second version) does not work properly. Therefore, in Windows 98, even if a "Database Maintenance Plan" is successfully created based on the preceding steps, the "SQL Server 2000 agent" service cannot be started and does not work.

2. the reason for selecting "Full backup" is that when you perform a full backup, SQL Server will. MDF and its corresponding. compare the LDF file, delete some old and unnecessary logs, and then. MDF and. LDF files are merged and compressed and stored together.

◆ Advantages: the database can be stored as full as possible.

◆ Disadvantage: The storage size increases with the increase of the database, and the storage time also increases with the increase of the database.

3. During the establishment of the "Database Maintenance Plan", readers should try their best to use various options and functions to gain a better understanding of the "Database Maintenance Plan.

4. Although there are many third-party backup software and tools on the Internet, most of them are shared versions. I did not try these third-party software because I was worried about intellectual property rights issues, viruses, and other issues. In this case, I am not clear about their performance. Maybe these software features are very powerful and can meet more and higher requirements. Here I just compare the "backup" tools and software that come with Windows 2000 and SQL Server 2000.

_______________________________________________________________________
If you want to elaborate on your point of view, please speak below