Creating Backup Jobs in SQL Server 2005

By : Serhiy Snisarenko Jan 09, 2007

Printer friendly

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 dba02\sql2005 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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder on the local database server. Below is the code fragment that utilizes the xp_sqlmaint procedure; the complete script can be downloaded here.

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 "dba02\sql2005" -D "AdventureWorks" -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG backup_aw.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\server02\dbbackup\sql2005" -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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\aw_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 \\server02\dbbackup\sql2005 has been selected.
  • Backup file extension — make sure that its value is bak without a leading dot.
  • Check the Verify backup integrity box.

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.