Membuat Backup Jobs di SQL Server 2005
Ini bagi DBA yang membuat Maintenance Plan untuk membackup database. Di SQL 2005 Maintenance Plan, kalau membuat suatu plan backup database ada satu pilihan yang hilang dari WIZARD pada SQL 2000 yaitu menghapus file dari hasil backup sebelumnya (Removing). Jadi kalau PLAN sudah dibuat maka file backupnya akan bertambah terus menerus setiap hari.
Tidak seperti SQL Server 2000, di SQL 2005 tidak ada pilihan Back up database task untuk menghapus file backup database yang lama (hasil backup lama). Agar dapat menghapus file backup yang lama (hasil backup sebelumnya), terdapat suatu tambahan task yaitu Maintenance Cleanup Task yang harus disisipkan dan dikonfigurasi. Drag dan drop task ini ke dalam panel desain dan double-click task tersebut untuk melakukan set properties: Berikut ini petunjuk penggunaan Maintenance Cleanup Task.
- Connections: Biarkan dengan default Local server connection.
- Delete files of the following types: Pilih Backup files.
- File location: Pilih Search folder and delete files based on an extension dan sediakan Path seluruhnya pada folder backup seperti yang anda lakukan pada Back up database task (di maintenance plan).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’\\server02\dbbackup\sql2005′, — 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.