SQL2005 automatic Backup, periodic deletion of maintenance plans and automatic periodic purge logs

As a DBA, their most common daily tasks are:

1) complete a full or differential backup of the database on a regular basis.
2) regularly clean up backup files, because storage space is limited, may only need to save a period of time files (such as within a week or January).

And how do you do this two points? I believe that the SQL SERVER2005 version before the implementation of this function, but also can be implemented, just to achieve regular cleanup of backup files is not so straightforward, generally require handwritten batch job maintenance, and in the SQL Server 2005 version, these features have been designed as prefabricated components, You just need to drag and set the relevant properties in the data maintenance plan, and the steps are as follows:

First step: Create a new maintenance plan

Figure 1

In server management \ Maintenance plan, right-click New Maintenance Plan.

Step two: Design a maintenance plan

2.1 Drag the Backup database task from the Task Toolbox in the maintenance plan to the Maintenance Plan designer.

2.2 Drag the Purge history task from the Task Toolbox in the maintenance plan to the Maintenance Plan designer.

2.3 Drag and drop the cleanup maintenance task from the Task Toolbox in the maintenance plan to the Maintenance Plan designer.

As shown in the following:

Figure 2

Right-click edit "Back Up Database" task to set properties such as:

Figure 3

This task will automatically complete the full backup job for the database.
Right click on edit "Clear history" task, such as:

Figure 4

This task automatically completes the Purge history job.

Right-click edit "Purge Maintenance" task, such as:

Figure 5

This task automatically completes the job to purge the historical backup files.

The job schedule properties for this maintenance plan task are set as follows:

Figure 6

This step completes the setting of the job properties. The system will run the maintenance job periodically, as set.

Step three: Save the maintenance plan

In this case, you can track the completion of this plan only by viewing the log records, and you can adjust and modify the maintenance plan according to the success record.

DBAs often use the most common backup maintenance plan for SQL Server 2005, which is as simple as this!

[SQL]View PlainCopyprint?

1. Use Xp_servicecontrol to start SQLServerAgent (SQL Server Agent)
2. EXEC master.dbo.xp_servicecontrol ' start ',' SQLServerAgent '

automatically periodically purge database logs

The database log files grow over time, and if they are not cleaned up for a long time, the files become particularly large and therefore need to be emptied periodically, but the day-to-date file is an important basis for recovering the database, and it is unwise to use it without a log file. Manual cleanup of a single database is OK, but the database is more, or there is no time to clean up, the hard disk space will be full, affecting access. Therefore, it is still more practical to set up automatic cleanup of database log files. Manual Cleanup Method: Right-click the database you want to clean, select Properties, on the options card, set the failure restore model to simple, OK to close, then right-click the database, all tasks-shrink the database, clear the log file after confirmation, and finally remember to re-select properties to set the failure restore model to full. Automatic Cleanup Method: also use the SQL Server Agent service to perform automated jobs. Open Enterprise Manager, go to administration-SQL Server Agent-jobs, right-click in the right-hand window and select New job. General tab, fill in the job name, describe it, and note that the owner is best to use SA or the default administrative account. Go to the Steps tab, create a new job step, fill in the step name, type script, database for the database that needs to clean up the log, and fill in the following command in the command below: DUMP TRANSACTION database name with NO_LOGDBCC shrinkfile (database log file Name, 1) Top database name fill in the database name that needs to be maintained, database log file name fill in its corresponding log file name, note, not in the explorer to see the name with the suffix, but in Enterprise Manager, database properties in the Log tab of the log name (do not take the suffix name), Add a job step after determining. If you need to maintain multiple databases, repeat the Add job step using the above method, note the action after each step succeeds or fail, and finally select the step to begin with. In the Schedule tab, a maintenance plan that resembles a backup, filling out the scheduling cycle, which is the periodic cleanup cycle, is no longer detailed. If necessary, you can set the notification item after the job is completed on the last Notification tab, set up the operator, and set up the appropriate service, which is not specified here, usually not ... Originally are manually performed above the various operations, more trouble, find on the Internet with their own practice, summed up the experience, the implementation of a period of time, indeed more worry than before. Archive one.

SQL2005 automatic Backup, periodic deletion of maintenance plans and automatic periodic purge logs