I recently wrote about a method for creating automated scheduled backups of SQL Server databases in installations where the management tools don’t provide the functionality.
This is great as a first step, but you’ll often want to get your backup offsite in case the server goes down.
First things first, download and install 7 zip. 7 zip is great compression tool for Windows that can manage zip and rar files (amongst others) and crucially has it’s own .7z format which offers a really high compression ratio (i.e. you end up with a smaller file!).
Sign up for a Dropbox account, download and install the program. Dropbox is essentially a cloud-storage and file sharing program, that gives you up to 2GB of space for free and can sync your files across however many computers you install it on.
Creating the backup schedule
- Open task scheduler from Start >> Administrative Tools >> Task Scheduler
- Click “Create Basic Task” and enter a relevant name, e.g. Daily 7z Backup
- Under “Triggers” select ‘Daily’ and then choose a time to run the backup – if you’re planning on using this on database backup files, make sure you leave enough time for the database backup task(s) to complete
- Under “Actions” select ‘Start a program’.
- In “Program/script” enter the filepath to the 7-zip exe file, e.g. ”C:Program Files7-Zip7z.exe”
In “Arguments” enter the following: a "C:\Path\to\Dropbox\MyBackUpFile.7z" "C:\Path\to\the\folder\to\be\zipped" -ms=off
Keep the double quote in place.
- In the first set of quotes enter the path & filename you wish to use – must be in your Dropbox folder for this to work. The script will create the file the first time it is run.
- In the second set of quotes enter the path of the folder to be compressed
- In “Start in” enter the path to your Dropbox folder, e.g. C:UsersSimonDropbox
- Save the new task
Testing the schedule
To test the task you can simply run it manually from Windows Task Scheduler.
If you want to test different settings you can run it from the command prompt by joining the “Program” and “Arguments” section together. For example
"C:\Program Files\7-Zip\7z.exe" a "C:\My Dropbox\SQLbackups.7z" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" -ms=off
Tips and gotchas
This method automates a backup into the cloud. If you want to bring them down to another machine simply install Dropbox and sign in with the same account.
Backing up the files in the way uses your bandwidth on a daily basis. If you’re creating HUGE .7z files this may not be the method for you. I had a scenario with a 1.5GB .7z on a remote server going up into Dropbox and coming down into a local server. The transfer times of this size file on a daily basis caused conflicts as the machines got out of sync causing DropBox to run out of space.
Clicky on the link for more information about using 7 zip commands.