Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating SQL Server password protected backups and cleanup tasks


By:   |   Read Comments (3)   |   Related Tips: More > Maintenance

Attend a SQL Server Conference for FREE >> click to learn more


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.
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.


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, April 16, 2010 - 1:28:20 AM - @tif Back To Top

@Noeldr. You have a valid point. Although in problem section of tip is mentioned about SQL Server 2005/2008 but it looks imperative to include the caution in Next Steps section for coming versions. I would post the update including this caution. Thanks for your participation and contribution.

 


Thursday, April 15, 2010 - 1:18:35 AM - @tif Back To Top

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? 


Wednesday, April 14, 2010 - 11:19:38 AM - Noeldr Back To Top

 It is all nice but the tip should have also included that:

 

" This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.  "

 

In big letters!


Learn more about SQL Server tools