Solution of SQL Server "Database Maintenance Plan": Jiangxi and Jiangsu Yu
As a network administrator, regardless of the size of the network is large or small, in addition to maintaining a smooth operation of the network in daily management, the key data in the backup network is also very very very very. Important work link. Various faults in the network are not divided into two: software failures and hardware failures. For "hardware failure", it can be resolved in time by repairing or replacing hardware devices; for "software failure", you can resolve it by reinstall or upgrading software, redo network or application software system, and use this method to solve network failures. Most of them need some basic, key data support can be restored to normal. However, key data such as such a network (especially key data in the "Application Software System") is damaged or lost, and most of them cannot recover and make up. Even if some data can be restored, compensate for the cost of them (such as time, manpower, financial, material, etc.) may far beyond the company's ability. Therefore, the backup work of focus on data is a task that must be concerned in the daily management of network administrations, and it is also a work that must be repeatedly operated. Directory Current Backup Strategy Specific Realization Steps Conclusion The Current Backup Strategy When I have built a LAN With Windows 2000 Server as the operating system, SQL Server 2000 is a software platform for the application system for the LAN to build a local area network, and the computer of the core sector such as the local area network will be closely connected by the local area network platform. In this way, each core department is in time, dynamically stored in the SQL Server 2000 database on the core DELL server of the company's LAN, and provides the various data services required to all parties in this basis. . Therefore, since the date of formal operations starting from the company LAN, I value the key data in the LAN-especially these business data in the local area network. At the same time, I also want to back up data software to implement the following automatic functions. Function 1: Enables a certain moment of every day (such as 0:00:00, of course this time is independently set) All key databases including all business databases are "fully backed up". Function 2: Perform a difference backup of the transaction logs of the various databases involved in Function 1 every day within 0:00:00 to 23:59:59. Function 3: Every day, it is possible to reserve the most recent two days of backup generated in the function 1 and function 2 (ie: the previous day and the first two days), and it is possible to automatically delete for two days ago. Backup of all databases and transaction logs. Function 4: Regular (such as every week) backs up the copy of the full backup of all critical databases to the tape or other storage medium (this part of the work may require manual completion). Thus, the implementation of these functions, in daily management work, I tried the software and methods of various backup data, such as the "backup" tool that comes with Windows 2000, "backup" features comes with SQL Server 2000 Wait. These backup software and methods have a thousand autumn, but there are the following shortcomings: one requires artificial intervention and cannot implement automatic backup (such as "backup" tools that come with Windows 2000). If this method is used, it must be manually backup, in case the day does not back up due to business trips or other reasons, then the server or data failure is there, then the trouble is big. The other is to implement automatic backup, but old backups cannot be automatically deleted (such as "backup" feature comes with SQL Server 2000). If this method is taken, you must manually remove the old backup in time, otherwise the big hard disk will be quickly used.
After comparing each other, I decided to use the "Backup" feature comes with the second -SQL Server 2000 to back up the critical database because it enables the "Auto Backup" function, which is slightly better than the first. Therefore, in a period of time, the first thing I go to work every day is to first check the new backup of the various data in the backup directory, and then manually delete the old backup data. This kind of practice once makes me very distressed. One day, I am inadvertently read the contents of the Automation Management Task when using the SQL Server 2000 "Help" to query the semantic explanation of a transact-sql statement. After reading from the head to the tail, I can't help but look, the original SQL Server 2000 itself came with a powerful function that enabled my backup requirements - "Database Maintenance Program". So I immediately followed the content of this part, step by step in one step in one step in one step in a database, and successfully created a database maintenance plan. After a week of trial operation, this program can automatically back up scheduling, and automatically delete old data backups, fully meet my backup requirements. Since then, I use SQL Server 2000's "Database Maintenance Scheme" to back up all critical databases, and rigorously, perform functional regularly, back up complete backend databases to other clients in Tape and LAN every Friday. Mainly on the hard disk of the network management PC for network management. The purpose of this is to save three identical backups at the same time, reducing the loss of failure. Moreover, through the "Database Maintenance Program" of SQL Server 2000, I can now easily back up the required data to facilitate managing their backups, reducing the daily workload, and reducing some of the working pressure. Specific implementation steps
Contents: Open SQL Server Enterprise Manager Form 2: Find the "Database Maintenance Plan" function Step 3: Create "Database Maintenance Plan" Step 4: Maintenance and Management "Database Maintenance Plan" fifth Step: Start the SQL Server 2000 agent to perform the Job Sixth: Check the Results The Database Maintenance Plan feature can be found in the Enterprise Manager of SQL Server 2000. Note: 1. The following operation is to operate on the Windows 2000 Server of the server. The same operation on the Window 9x system. 2. Since SQL Server 2000 performs a backup, a number of files will be generated (especially when transaction log backup), so it is recommended to establish a separate backup directory to store in accordance with the database name. 3. All of the following processes generally do not affect the use of the database. Step 1: Open the SQL Server "Enterprise Manager" form Click Enterprise Manager in the "Program (P)" menu under the "Program (P)" menu on the taskbar "Menu item, you can open the Enterprise Manager form of SQL Server 2000. Step 2: Find the "Database Maintenance Plan" function In the Tree Tab other in the "Enterprise Manager" form, click " " icon with the mouse to expand "Microsoft SQL" under "Control Bunder" ", You can see there is a" SQL Server Group "; then continue to extend the" SQL Server Group ", at this time, you can see the name of the server appear (" jxnc-server "in Figure 1 is me. The name of the server); continues to extend this server, you can see that there are items such as "database", "data conversion services", and finally click the "Management" project, you can see there is a "database Maintenance plan "(Figure 1).
figure 1
Click the Database Maintenance Scheme project, and the existing maintenance plan project will appear on the right side of the Enterprise Manager window. Each maintenance plan includes the following items: 1. Name: is the name of the maintenance plan. This name can be customized, all in Chinese. 2. Database: This is the name of the database for maintained by the maintenance plan. Because a maintenance plan allows simultaneous maintenance of multiple databases, you can display the name of multiple databases (in Figure 1, you can see "Database" in a database maintenance plan called "System Database Backup", including three Database: Master, Model, and MSDB). 3. Server: The name of the server where the database maintained by the maintenance plan is also maintained. "(Local)" indicates that it is a local server. 4. Countermeasures: refers to the contents of the specific maintenance work required for the maintenance plan. There are 3 "Database Maintenance Plan" in Figure 1, which is "Database Backup, Transaction Log Backup", which means that these maintenance plans are simultaneously "database" and "transaction logs" in these maintenance plans. Step 3: Create a Database Maintenance Plan mouse right-click the Database Maintenance Plan project, select the New Maintenance Plan (P) "function, open the Database Maintenance Plan Wizard Form, which can create a new one in accordance with this wizard Database Maintenance Plan. Step 1: Click the "Next (n)" button to open the Select Database Form (as shown in Figure 2). One or more databases can be selected as an operation object in this form. For the sake of narrative, I only selected a database "Regie" here. figure 2
Step 2: Click the "Next (N)" button in Figure 2 to open the "Update Data Optimization Information" form (Figure 3).
image 3
In this form, the data and indexes in the database can be re-organized, and if they can be set, the maintenance schedule automatically deletes unused space in the database in order to improve performance. However, it should be noted that in this form, as long as the "reorganization data and index page [R]" check box, "update the statistics used by the query optimizer. Sample [D]" check box Failure (become gray, can't choose). Moreover, "reorganizing the data and index pages [R]" check box and "Delete Unused Space from the Database File [M]" checkbox as long as there is one selected, "Scheduling [S]" Features is valid. Click "Change the [C] button to customize" scheduling ". The readers can determine whether to use the functions according to their own situation. Of course, you can also view the specific meaning of each function by clicking the Help button. In this form, you can easily set the continuous runtime and running frequency of each job. After completing your own settings, you must select the "Enable Dispatch [B]" checkbox in the upper right corner, such a job scheduling is really completed. Step 3: Click the "Next (N)" button in Figure 3 to open the "Check Database Integrity" form. In this form, you can set a maintenance plan to automatically check the integrity of the database before the backup database, in order to detect inconsistencies due to hardware or software errors. In this form, only "Check the Database Integrity [H]" check box, "Backup" check [R] "and" Scheduling [S] "feature are valid. Click "Change the [C] button to customize" scheduling ". Readers can decide independently, better ways is to select "Check the Database Integrity [H]" checkbox (recommended because some errors may be corrected). Step 4: In the "Check the" Next (N) "button in the Factory Integrity Form, open the" Specify Database Backup Plan "form. If you need to back up the database, you must select "Part of the Maintenance Plan to back up the database [A]" check box, and you must specify the location of the storage backup file: tape [P] or disk [K]. If you select "Disk [K]" as the location of the database backup, set "After the schedule" After Click "Next (N) button," Specify Backup Disk Directory "form (Figure 4).
Figure 4
In Figure 4, you can specify a directory of the storage backup file (you can use the default directory, you can customize), backup file extensions, and can indicate that the backup plan is automatically deleted as a certain time (set in Figure 4) It is "2 days", which means that all backup files before two days will be automatically deleted, leaving only the backup files of the last two days). The "Creating a subdirectory [C]" function for each database is only useful when multiple databases are selected in step 1, and there is little effect on a database. After setting, click "Next (n)" button to display the "Specify Transaction Log Backup Plan" form. If you select "Tape [P]" as the location of the database backup, set "Scheduling" After Click "Next (N) button directly displays the" Specify Transaction Log Backup Plan "form. Step 5: Specify the "Transaction Log Backup Plan" process is identical to the process of step 4, just a slight difference on the setting "Scheduling" (because my requirements are backed up every day, the transaction log is backed up every hour) . Step 6: After all of which are set to the backup plan of the transaction log, click the Next (N) "button to display the" Report "form. In this form, you can specify a directory for storing logs during execution of the entire backup plan. The setting process is similar to that of Figure 4. Figure 5
Step 7: After completing step 6, click "Next (n)" button to display the Maintenance Plan Historical Record Form. In this form, you can specify how history of this maintenance plan (is stored on the Local Server, or on the "Remote Server"), and through the number of rows in the specified table, you can define the storage size of the history record. Step 8: After completing step 7, click "Next (n)" button to display the "Complete Database Maintenance Plan Wizard" form (Figure 5). In this form, you can customize a "planner name [P]" (recommended, so that manifest and identification), of course, the default "plan name [P]" can be used. Moreover, it can be confirmed by confirming the content in the text box under "Plan Name [P]". If it is incorrect, it can be returned to the corresponding form by clicking the "Previous [B]" button in the form. modify. Step 9: After completing step 8, click the "Complete" button to display the "Maintenance Plan Create Success." The prompt box, then click the "OK" button to successfully set a new database maintenance plan. As can be seen from Figure 6, a new database maintenance plan has been successfully created - "Regie Backup". Step 4: Maintenance and management of the "Database Maintenance Program" is complete, it is very convenient for everyday maintenance and management of each "database maintenance plan", just double-click "Database Maintenance Plan" to the third step. The content involved is changed, corrected.
Figure 6
As shown in Figure 6, the mouse right-click "Regie Backup", click "Attribute [R]", or directly double-click "Regie Backup" to open the Database Maintenance Plan Form. In this form, all functions involved in the third step are integrated, each function can be arbitrarily modified, and the modification process is the same as the corresponding step in the third step. However, it is to be explained that when the "Text Report" in the "Text Report" under the Reports tab in Figure 7 is the third step in the third step when the text report file [f] "option is earlier than this time. In step 6, whether you set it into "minute", "hour", "day", or "month", it will be automatically corrected "Week" after the creation is successful, and in the future, there will be no matter how to modify it. After saving, it will still be displayed as "week", but other options are still valid.
Figure 7
Step 5: Start the SQL Server 2000 agent to perform the "Job" to complete the third step, you need to start the SQL Server 2000 Agent to perform the Database Maintenance Plan job. As with the steps to expand SQL Server 2000 "Database Maintenance Plan", in the Manage project, you can find a "SQL Server Agent" (Figure 8).
Figure 8
Click the Job sub menu under SQL Server Agent, and the existing job items will be displayed on the right side of the Enterprise Manager window (you can see there in Figure 8 already existing. Each job item includes the following data columns: 1. Name: Of course, the name of the job, can be customized, all in Chinese. In order to understand convenience, it is recommended to combine Chinese and English. Whenever a "Database Maintenance Scheme" is created, the following default name will be automatically generated: (1) When the "Backup Database" function is set in the new "Database Maintenance Plan", the default name "DB maintenance Plan a job for DB backup jobs in '*******'. (2) When the "Backup Transaction Log" function is set in the new "Database Maintenance Plan", the default name "DB Maintenance Plan '******" "is generated" operation. The Plan Name in the Database Maintenance Plan is displayed in the "*******" in the above (1) and (2) ("Plan Name" set in Step 8). 2. Category: Indicate the category currently belonging this job. The default is "[Uncategory (Local)]". 3. Enable: Indicate whether the job is in the "Enable" state. 4. Operation: Indicates if the job is in the "run" state. 5. Distributed: Indicates whether the job is in the "scheduled" state. 6. Status: Indicates the current operating state of the job - not running, running. 7. Last operation status (start date): Show the status after the recent run ("success", "failed", or "unknown"), date and time at runtime. 8. Next time: Indicates the date and time of the next run. As shown in Figure 8, the mouse right-click "Regie Full Backup" job, click Properties [R] ", or directly double-click the" Regie Full Backup "job, open the" Properties "form of the job. The detailed configuration items of the job are integrated in this form. Each configuration item can be arbitrarily modified. There are four tabs in the "Properties" form: ◆ General: You can reset the job name in this tab ("Name [N]" text box), modify the classification of the job ("Classification [Y] drop-down box ), The owner of the specified job ("Owner [W]" drop-down box), simply describe the job ("Describe [R]" text box), and decide whether to enable this job ("Enable [E]" Selection box). ◆ Steps: You can create, insert new steps, delete, and edit existing steps in this tab. Click the Edit [E] button, you can review the execution statement of the job in the "General" tab in the "Convention Job Step" form in the Edit Job Process. ◆ Scheduling: You can create a new schedule, new alert, delete, and edit existing schedule in this tab. ◆ Notice: In this tab, you can set the operation when the job is completed (ie, when the job is successful, the job failure) is transmitted, that is, send an email, paging operator, and network alert information, and write to the Windows application event log. , Automatic deletion and other operations. Step 6: After the results have been established after five steps described above, a full backup database is established. You can check if there is a corresponding backup file in the backup directory by "Resource Manager".
After a long time, if you correctly establish a complete database maintenance plan if you reserve a 2-day database full backup and 2 days of transaction log backup, you will have a complete database maintenance plan, whenever viewing files in the backup directory, all files should exist 102: ◆ 2 x full backup of the database, ie two files "database name _db_yyyy mmddhhss.bak" format as the file name; ◆ 3 Ge with a full backup of the database corresponding to The record report file of the operation process is filed in the file name of the "Database Name" "Backup 4_YYYMMMDDHSS.TXT" format; ◆ Difference backup of 48 transaction logs, 2 days of 24 database names _tlog_yyyyyymmddhhss. TRN "Format is file name; ◆ 49 Record report files for the corresponding operation procedure for the difference backup of the transaction log, that is, the file name file named" Database Name "Backup 6_YYYYMMDDDHSS.TXT" format. Among the above file names, "Database Name" is the name of the database selected in the steps of the third step; "YYYYMMDDHSS" is a timestamp, its format is: "YYYY" refers to "Year" (4 digits), "MM" "Refers to" Month "(2 digits, less than 2 bits of" 0 ")," DD "refers to" Japan "(2 digits, less than 2 bits of" 0 ")," HH "refers to" Time " 2 digits, less than 2 bits of "0"), "SS" refers to "points" (2 digits, less than 2 bits of "0"). Conclusion All data generated by various services that occur in daily operations in a business, gradually become a company's wealth and capital through computers. With a computer, you can make it easy to analyze some or all of the data, through various forms of feedback (such as chart, table, etc.), to the company's decision-making layer for reference, which is convenient for guidance and help for the company's future decision-making. Based on this, the value of these data is exhibiting geometric velocity over time. So I think that the backup of the data is one of the most important work in the daily work of the network administrator. Through this "Database Maintenance Program", I have a slight feeling: 1. After so long-term use, I think "Database Maintenance Program" still has shortcomings, although the "Database Maintenance Program" is very powerful, but it The final result is to generate a job, which is periodically executed by the "SQL Server 2000 Agent" service to complete the backup of the database. This requires the "SQL Server 2000 Agent" service to "run" normally. From multiple installations, the "SQL Server 2000 Agent" service in the Windows 2000 system can run normally, and can run automatically with Windows 2000. But in the Window 98 system (including the second edition), it cannot be run normally. Therefore, even in the Windows 98 system successfully created the "Database Maintenance Plan" in accordance with the above steps, it will also become no role because the "SQL Server 2000 Agent" service cannot be started. 2. The reason why chooses "full backup", mainly, SQL Server will compare .mdf, to its corresponding .ldf file, delete some old, unnecessary logs, and then .mdf and .ldf The file is combined, and the compressed is stored. ◆ The advantage is that the database can be saved to the most possible ◆ Disadvantages are: The storage amount increases with the increase of the database, and the storage time will also extend as the database increases. 3. In the establishment of the Database Maintenance Program process, readers should use various options and functions as much as possible in order to deepen the understanding and master of the Database Maintenance Plan. 4. Although there are many third-party backup software and tools on the Internet, most are shared version.
