Creating SQL Server password protected backups and cleanup tasks

By: Atif Shehzad | Updated: 2010-04-14 | Comments (3) | Related: > Maintenance

Problem

I have read the tip about securing SQL Server backups with a password. It is great option and I am planning to implement it for my production servers on SQL Server 2005 and SQL Server 2008. I am currenlty using a backup plan configured through maintenance plans, but I want to be able to create password protected backups. So my requirements are:

To create scheduled password protected backups
Preserve previous backup files for required time span
Delete backup files older than specified time

In this tip I will show you how this can be done.

Solution

Maintenance plans create scheduled jobs for both backup creation and deletion of old backup files. Keeping this in mind we will create password protected backups through a scheduled job.

For removing old backup files, we have several options depending upon the requirements. These options may be:

In this example we will continue to use maintenance cleanup tasks in the maintenance plan for removal of old backup files, but feel free to read the other tips above for other options.

We will use the AdventureWorks database for our example, first we will create a scheduled job with a somewhat dynamic password and then we will configure a maintenance plan clean up task for removal of old backup files.

Prepare script for password protected backups

Before proceeding with the job creation steps, let us first go through the code that is used to create password protected backups.

--Script 1: Create password protected backup

DECLARE @PathAndName varchar(150) -- Path and name for backup file
DECLARE @Pwd varchar(25) -- Password created with date combination
SET @PathAndName = 
'D:\Testing\AdventureWorks-'+
replace(convert(varchar, getdate(), 120),':','-')+'.bak'
SET @Pwd = replace(convert(varchar, getdate(), 102),'.','-')+'-MSSQLTips'
BACKUP DATABASE AdventureWorks 
TO DISK= @PathAndName
WITH MEDIAPASSWORD = @Pwd
GO

Variable @PathAndName is used to store the path along with name of the backup file. As configured the backup file would be created with a name such as AdventureWorks-yyyy-mm-dd hh-mm-ss.bak. You may provide a path and name suitable to your system.

Second variable @Pwd is used to store a slightly dynamic password. The password format is yyyy-mm-dd-MSSQLTips, where yyyy, mm and dd are from the backup creation date. Again you can set your own choice for password with the @Pwd parameter.

This script would be used to create backups through a scheduled job. Note that the password for each day will be different due to change in creation date that we used in password combination. This may not be the best choice, but I wanted to keep this simple to show how this can be done.

Scheduled job for password protected backups

Now that we have a script ready to create password protected backups, let's start to create a job for full database backups.

First, verify that SQL Server Agent service is running. Go to SQL Server Agent in SSMS and the icon should show a green arrow.
In SSMS go to Jobs folder under SQL Server Agent service and right click on jobs folder to create a new job

verify that SQL Server Agent service is running. Go to SQL Server Agent in SSMS

A frame will appear to configure various settings for the scheduled job. The left panel of the frame contains links for different configuration pages. The frame will open with the default page 'General'. Enter name, owner, description etc for the scheduled job.

In SSMS go to Jobs folder under SQL Server Agent service

Now click Steps in page list in left panel. Then click New in right panel of frame. Another frame will appear for configuration of actual job steps to be executed as shown below. Enter step name and paste script 1 provided above in command section. For minimal configuration here, we will not provide any other parameters. Now click OK to save this information.

Now click Steps in page list in left panel. Then click New in right panel of frame

After clicking OK in Job Step properties, we are once again in the primary frame for this job. Now click Schedules page in page list in left panel of primary frame. An empty list of schedules for this job would appear in right panel. Click new and another frame will appear for configuration of backup interval and timing. Provide the parameters to achieve planned interval and time of backup and click OK.

Click new and another frame will appear for configuration of backup interval and timing

When we are finished with schedule page, we are again on the primary frame. Click OK on primary frame to completely finish the creation of the job for creating password protected backups.

Configure cleanup task for old backup files

In second phase, we have to configure the maintenance clean up task for removing old backup files. For this purpose we will make use of maintenance plan designer.

In SSMS, right click on Maintenance Plans folder and select New Maintenance Plan...

In SSMS, right click on Maintenance Plans folder and select New Maintenance Plan

Provide Name of maintenance plan.

The maintenance plan will be opened in the designer. Maintenance plan tasks would appear on left side in the toolbox and designer pane for default subplan_1 would be on right side. Drag the Maintenance Cleanup Task from toolbox to designer pane.

Drag the Maintenance Cleanup Task from toolbox to designer pane

Double click the dragged Maintenance Cleanup Task or right click on it and select edit. Provide configurations for cleanup like target folder, extension, and age of backup files required to be deleted. Age would be determined according to task run time. In my example I am deleting files with a "bak" extension in the D:\Testing folder that are older than 1 hour.

Double click the dragged Maintenance Cleanup Task or right click on it and select edit

Click OK on cleanup task frame and now an important step is to schedule the cleanup task. Open schedule properties by clicking schedule in front of Subplan_1 in subplans list and set the schedule for how often this should run. This is the same form that we used to schedule the job above. Once this is done click OK to save. Then close the designer and save the new mainteance plan.

Open schedule properties by clicking schedule in front of Subplan_1 in subplans list and set the schedule for how often this should run

Review the configured plan and expected results

At the end we have a scheduled job to create password protected backups and also a cleanup task created through maintenance plans to remove old backups.

Next Steps

For SQL Server 2005 make sure that you have SP2 or later installed, because with SP1 there are reported problems of maintenance plans not removing old backup files.
After testing on test server also verify the expected results of plan on production server when deployed.
Note the password combination used to create backups, this will be needed for restores.
Password protected backups can not be restored through SSMS GUI. Read this tip to look at the T-SQL syntax to restore password protected backups. To make sure you have a complete understanding of the process, practice the restore of the password protected backups.
Click here to read in BOL about the maintenance cleanup task
It is important to note that using the MEDIAPASSWORD option for creating password protected backups may be removed in future versions of SQL Server. So avoid using this in your application code. While planning migration to any future version of SQL Server, make sure to modify and test script #1 used in the job step for backup creation.

About the author

Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2010-04-14

Thursday, April 15, 2010 - 1:18:35 AM - @tif	Back To Top (5242)
Thanks for pointing out. Please specify along with BOL reference which feature would be removed in future versions. Password protected backup creation or clean up task?