SQL Pruning Utility

A utility written in .NET to to prune SQL backup files from a given folder (or an Amazon S3 bucket).

The program lists all .bak or .sql files in a given folder or Amazon S3 bucket and determines which one to keep or prune.

Disclaimer

WARNING: This program is designed to delete files. The authors of this program do not accept liability for any errors or data-loss which could arise as a result of it.

Pruning Rules

1. Keep daily backups for two weeks
2. Keep One Sunday per week for eight weeks
3. Keep 1st and 3rd Sunday of each month for 52 weeks
4. Keep 1st Sunday of each year after that
5. When more than one backup per day, keep the most recent.
6. Prune anything else

Notes:

The pruning rules apply:

• Per database
• Only supports the Julian calendar
• Apply from the date of the most recent backup for a given database

Example of expected file names (as automatically generated by backup plans created in Microsoft SQL Management Studio):

dbname1_backup_2014_06_20_010002_0897411.bak
dbname2_backup_2014_06_20_010002_0957417.bak
...

The utility relies on the date in the file name, not the file system's creation date.

Usage:

Pruning Mode:

sqlprune.exe [path] -prune [-delete] [-no-confirm] [-aws-profile]

• prune: The flag to activate the 'prune' mode
• path is the path to a local folder or an S3 bucket containting .bak files (e.g. c:\sql-backups or s3://bucket-name/backups)
• --delete is a flag you must add otherwise files will not be deleted
• --ext is an optional parameter can use to restrict to different file extensions (see File Extensions)
• --yes is flag you can use if you don't want to confirm before any file is deleted
• --profile is optional and can be used to override the value of the AWSProfileName
• --profilesLocation is optional and can be used to override the value of the AWSProfilesLocation
• --region is optional and can be used to override the region when connecting to S3

Examples:

Simply list which .bak files would be pruned in a folder without deleting anthing (dry run):

sqlprune prune E:\Backups

Confirm before deleting prunable backups in E:\Backups, including sub directories:

sqlprune prune E:\Backups --delete

Confirm before deleting prunable backups for database names starting with test in s3://bucket-name:

sqlprune prune s3://bucket-name/test --delete

Recovery Mode:

sqlprune.exe [path] -recover -db-name -dest [-date] [-no-confirm]

• path: The path to a local folder or an S3 bucket containting .bak files (e.g. "c:\sql-backups" or "s3://bucket-name/backups")");
• recover: The flag to activate the 'recovery' mode
• path is the path to a local folder or an S3 bucket containting .bak files (e.g. c:\sql-backups or s3://bucket-name/backups)
• --dbName: The exact name of the database to recover (case sensitive)
• --dest: The path to a local folder where to copy the file to
• --date: OptionallySpecifies which date and time to retrieve
• --ext is an optional parameter can use to restrict to different file extensions (see File Extensions)
• --yes is flag you can use if you don't want to confirm before any file is recovered
• --profile is optional and can be used to override the value of the AWSProfileName
• --profilesLocation is optional and can be used to override the value of the AWSProfilesLocation
• --region is optional and can be used to override the region when connecting to S3

When multiple backup files are found the most recent is used.

Examples:

Copy the most recent backup available for the database helloworld from an S3 bucket:

sqlprune recover S3://bucket-name/test --db-name helloworld --dest E:\Backups

Copy helloWorld_backup_2014_06_20_010002_0957417.bak from E:\Backups to the C:\destination:

sqlprune recover E:\Backups --dbName helloWorld --date 2014-06-20T01:00:02 --dest C:\destination

Download & Install:

1. Find the latest release.
2. Extract the zip in a folder.
3. Run the command from the command line prompt.

File Extensions

By default the prune and recover commands restrict to files ending with .bak extension.

You can override this search pattern with the -matchExpression parameter. Comma separated values can be used.

Example:

• Use a different file extension for your backup files: -file-extensions .backup
• Match on multiple file extensions for your backup files: -file-extensions .bak,.bak.7z,.bak.rar,.sql,.sql.gz

S3 Credentials and region

You can ignore this completely if you just want to prune files from a local folder.

sqlprune.exe will load default credentials configure with aws configure command.

Optionnaly you can specifiy:

• A different profile name with the --profile parameter
• A different profiles locaiton with --profilesLocation
• A different region with --region

TODO:

• Cutomisable pruning rules:
  • Handle recovering to an S3 bucket as well as a local path
  • Load the rules from a configuration file (yaml or json files)? The pruning rules would apply one after the other

Unit Testing

Veryfying which date should be kept or pruned is a tedious task.

To make it easier, we have modified an SVG visualiser to render the output of the unit tests into a familiar calendar view.

After you run the unit tests in PruneServiceTest, open Calendar.html in a modern web browser.

If your web browser doesn't have access to you local file system (e.g. Chrome) it will refuse to load the .json file.

Example:

####License

This project is licensed under the terms of the MIT license.

By submitting a pull request for this project, you agree to license your contribution under the MIT license to this project.