Creating SQL Server password protected backups and cleanup tasks
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.
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:
- Using maintenance plan cleanup tasks
- Using the FORFILES Command
- Maintenance task through VB script
- Using CLR function
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
- 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.
- 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.
- 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.
- 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...
- 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.
- 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.
- 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.
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.
- 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
View all my tips
Article Last Updated: 2010-04-14