The SQL Server 2005 Maintenance Plan feature has been significantly modified in comparison with SQL 2000: now it utilizes new Integration Services. Also, creating database and transaction log backups is not as clear as it was in SQL 2000. This article does not describe all available SQL Server 2005 backup features or provide some tricks dealing with them; instead, it offers solutions for the most commonly used backup jobs.
Using SQL Server 2000 Backup Job Scripts
If you created backup maintenance plans in SQL 2000, you probably noticed that the key element of the backup job was the xp_sqlmaint extended stored procedure, which used the sqlmaint utility. Despite the fact that Microsoft has deprecated both sqlmaint and xp_sqlmaint, and is planning to remove them from future versions of SQL Server, they are still here and they work well. So, you can take your existing SQL 2000 backup jobs, modify the server and database names, the backup files folders, the output and report files, etc., and run those scripts on your SQL 2005 server.
In case you do not have those scripts, here is an example of a database backup job that uses the xp_sqlmaint procedure. It runs a full database backup of the AdventureWorks database on the dba02sql2005 instance to the shared dbbackup folder on the server02 server, deletes backup files older than 4 days, and stores a report into the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG folder on the local database server. Below is the code fragment that utilizes the xp_sqlmaint procedure; the complete script can be downloaded at this locationSQL Server Backup Job Script.
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = N’Step 1′,
@command = N’EXECUTE master.dbo.xp_sqlmaint N”-S “dba02sql2005″ -D “AdventureWorks” -Rpt “C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG backup_aw.txt” -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB “/server02dbbackupsql2005″ -CrBkSubDir -DelBkUps 4days -BkExt “BAK”"‘,
@database_name = N’master’,
@server = N”,
@database_user_name = N”,
@subsystem = N’TSQL’,
@cmdexec_success_code = 0,
@flags = 4, –Overwrite output file
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGaw_backup.log’,
@on_success_step_id = 0,
@on_success_action = 1, — (default) Quit with success
@on_fail_step_id = 0,
@on_fail_action = 2 — (default) Quit with failure
Creating a SQL Server 2005 Maintenance Plan
If you are going to use new Maintenance Plan features, I strongly recommend installing SQL Server 2005 Service Pack 1 first. Among the new features and improvements that are included in SQL Server 2005 Service Pack 1, there is a fix for the previously existing discrepancy between Back up database task, which allowed storing database backups in separate folders, and Maintenance Cleanup Task, which could not delete backup files from those subfolders.
In order to create a new maintenance plan in Management Studio, first connect to the target server using Windows Authentication, then right-click on the Maintenance Plan folder in Object Explorer, select New Maintenance Plan, and enter the plan name. As a result, a Maintenance Plan design panel will appear on the right, and a toolbox with available Maintenance Plan Tasks will be displayed on the left. Click on the Connection button to verify that the current connection uses Windows Authentication (recommended by Microsoft). Currently connected Windows user will become the owner of the job created by this maintenance plan.
The first step in creating a database or transaction log backup is to drag and drop Back up database task from the toolbox to the design panel. Then double-click on that item to set the following necessary properties:
- Databases: Click on the dropdown field to bring up the database selection window. For this example, I chose Northwind and Pubs as Figure 1 shows:
Figure 1: Database selection window. - Backup type: choose Full.
- Destination parameters:
- Back up to — choose Disk.
Make sure that the Create a backup file for every database option is selected and the Create a sub-directory for each database box is checked. - You can use the default destination folder or specify your own. For this example, the network folder /server02dbbackupsql2005 has been selected.
- Backup file extension — make sure that its value is bak without a leading dot.
- Check the Verify backup integrity box.
- Back up to — choose Disk.
When you are done, the Back up database task properties window should look like the one shown on Figure 2.
Figure 2: Back up database task properties window.
Having verified the task properties, click OK. Now you can create a schedule for this plan. Click on the … button located to the right of the Schedule field and set the necessary parameters in the Job Schedule Properties window. Schedule name will be defaulted to <maintenance_plan_name>-Schedule and can be modified if necessary. The other parameters are similar to SQL Server 2000 schedule setup. As you can see in Figure 4, the backup has been scheduled to run every day at 1 a.m. Click OK to save this schedule.
When you click on the Logging button, the Reporting and Logging window appears. It is similar to the SQL 2000 Reporting tab: the default folder for the backup log is C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG, and it can be modified. You can also choose between the Create a new file option that will create a new log file named <maintenance_plan_name> _yyyymmddhhmm.txt for every completed job, and the Append to file option (in this case you have to name the log file). You can also check the option to send an e-mail to the operator.
Unlike SQL Server 2000, there is no option in the Back up database task for removing database backup files from the backup folder. In order to delete obsolete backup files, an additional Maintenance Cleanup Task must be inserted and configured. Drag and drop this task into the design panel and double-click on it to set the properties:
- Connections: keep the default Local server connection.
- Delete files of the following types: choose the Backup files option.
- File location: choose Search folder and delete files based on an extension and provide the same full path to the backup folder as you did on the Back up database task.
The file extension also should be the same as that used in Back up database task: bak for the database backup files or trn for the transaction log backup files (both without a leading dot). If you created a separate directory for each database in Back up database task, you can also check the Include first-level subfolders box. - File age: check Delete files based on the age of the file at task run time and set the Delete files older than the following option. The smallest retention period is 1 day now, no more hours and minutes as it was in SQL 2000. If you click on the View T-SQL button, you will see a new undocumented stored procedure, xp_delete_file, with the following parameters:
EXECUTE master.dbo.xp_delete_file
0, — delete files
N’/server02dbbackupsql2005′, — full path to the main directory
N’trn’, — file extension
N’08/08/2006 13:29:51′, — delete files created before this timestamp
1 — Including first-level subfolders
After the properties have been specified, the Maintenance Cleanup Task properties window should look like this:
Figure 3: Maintenance Cleanup Task properties window.
The Maintenance Cleanup Task should be executed only upon successful completion of the Back up database task. To ensure this, we have to link both tasks with a constraint. Click on the first task and then drag an available component output (depicted as a green arrow) to the next task. Double-click on the arrow to check and edit, if necessary, the Precedence Constraint properties:
- Evaluation operation: Constraint.
- Value: Success.
- Multiple constrains: By default, it is Logical AND. In this case, it does not matter because we only have one constraint.
Now we are done with all the tasks and the design panel should look like the one shown in Figure 4:
Figure 4: Design panel example.
Click on the Save All icon to save the plan. As a result, a new job, with the same name as the maintenance plan name, has been created and you can see both the plan and the job in the Object Explorer panel.
We can create another maintenance plan for s transaction log backup. Before we do this, let us clarify the existing maintenance plan name by changing it from Pubs_NW_backup to Pubs_NW_db_backup. Right-click on the plan icon, select the Rename option, edit the plan name, and press Enter. The corresponding job will be renamed too.
Before you create a plan for a transaction log backup, you need to verify that the target database recovery model is either Full or Bulk logged.
Creating this plan is similar to the full database backup plan creation. There are only two different parameter values for Back up database task: Backup type should be set to Transaction Log, and Backup file extension should be set to trn; also, for Maintenance Cleanup Task the File extension parameter should be set to trn. Schedule this plan to run every several hours during business hours and save it.
It is not necessary to create a Maintenance Cleanup Task in the transaction log backup plan in case you stored both the database and the transaction log files in the same folder. Instead, you can reuse the Maintenance Cleanup Task from the database backup plan by modifying the file extension parameter from bak to * (asterisk).
A differential database backup was not included in the SQL 2000 maintenance plan. Additional efforts were required in order to add a differential backup job and complete the database backup schedule. Now it is much easier. The Back up database task allows you to schedule a differential database backup the same way as a full database backup. I would recommend storing differential backup files in the same folder with full database backup files so you can use the same cleanup task to delete all of them.
You can test these plans/jobs the same way as in SQL 2000: just right-click on the plan icon in Object Explorer and select Execute from the dropdown menu, or right-click on the job and select Start Job at Step…. Please note that if you execute a maintenance plan, the status could be Success even if the job has actually failed (see Figure 5), or vice versa, but the job always returns a correct status message (see Figure 6).
Figure 5: Success status message.
Figure 6: Failure status message.
Checking the results
The plan/job results will be stored in the msdb database and in the file specified in the plan Logging property. If you checked the Log extended information box in the Reporting and Logging window during maintenance plan creation, the detail information will be stored in both the log file and the msdb.dbo.sysmaintplan_logdetail table. If you left this box unchecked and the job status is Succeeded, then there are no details provided in the log file or the msdb.dbo.sysmaintplan_logdetail table. However, if any of the tasks failed you can find all the details there.
You can also check the results in Management Studio by right-clicking on the plan or job icon and selecting View History from the dropdown menu, then expanding the record for a failed plan/job.
Figure 7: View History example.
If you work in a multi-server environment and you need to automate the review and analysis of the backup results, you can use the intended stored procedures, or query the appropriate tables and views directly.
The sp_help_jobhistory procedure can check the history of the job regardless of the way the job was created: either using modified SQL Server 2000 scripts or scripts generated by SQL Server 2005 Server Management Studio. Here is the example:
EXEC msdb.dbo.sp_help_jobhistory
@job_name = N’Pubs_NW_tl_backup’,
@mode = N’FULL’;
You can also query the msdb.dbo.sysmaintplan_plans view and the msdb.dbo.sysmaintplan_subplans, msdb.dbo.sysmaintplan_log, or msdb.dbo.sysmaintplan_logdetail tables to obtain maintenance plan history. For example, the following query returns information about a failed Pubs_NW_tl_backup maintenance plan:
SELECT mp.[name],
msp.subplan_name,
mpl.start_time,
mpl.end_time,
mpld.command,
mpld.error_message
FROM msdb.dbo.sysmaintplan_plans mp
JOIN msdb.dbo.sysmaintplan_subplans msp ON mp.id=msp.plan_id
JOIN msdb.dbo.sysmaintplan_log mpl ON msp.subplan_id=mpl.subplan_id
JOIN msdb.dbo.sysmaintplan_logdetail mpld ON mpl.task_detail_id=mpld.task_detail_id
WHERE mp.[name]=N’Pubs_NW_tl_backup’ AND mpl.succeeded = 0
ORDER BY mpl.start_time DESC;
Useful Tip
If you need to run an ad-hoc backup of your database without impacting the backup sequence, you can do it using the COPY_ONLY option:
BACKUP DATABASE AdventureWorks
TO DISK = ‘/server02dbbackupsql2005AdvWorksData.bak’
WITH COPY_ONLY;
Conclusion
Even though your existing SQL Server 2000 scripts can be helpful in jump-starting your database backup development in Server 2005, I would still recommend gradually moving to the 2005 methodology. Nevertheless, both methods currently work, and if this article helped you simplify database backup implementation, then it has done its job.
引用自:http://www.sql-server-performance.com/2007/creating-backup-jobs/
扫一扫
2448
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
