This method uses built-in system features and free software to make local and rotating offsite backups of SQL server databases.
Rsync Server – Openfiler
Backups are stored on an Rsync server. Openfiler is a NAS with web-configurable rsync support. To change permissions or settings of the rsync module, see Openfiler -> Shares -> backup_data. Database backups are put in a subfolder called ‘databases’. The ‘nobody’ user must have access to this folder, since that is the username the rsync client will use.
Backup rotation is achieved using Openfiler’s logrotate. Backup rotation (frequency, number of days, etc.) is configured in /etc/logrotate.d/db-rsync. Currently, backups are rotated daily for 7 days. Compression is enabled to save space.
Database Server
First, a backup script is created for each database:
- Create a directory to store local backups and scripts (eg. C:\Backup and C:\Backup\bin)
- Use SQL Server Management Studio to login to the database server
- Select the database to backup, then Right Click -> Tasks -> Backup.
- Set Backup type to Full
- Change the Backup destination to C:\Backup, and give the filename a ‘bak’ extension
- Click Script -> Script Action to File and save the script in the C:\Backup\bin directory with an ‘sql’ extension
Next, install cwRsync. Download and save db-backup.bat in C:\Backup\bin and modify it as required:
- change the sqlcmd lines to match your server and database.
- CWRSYNCHOME is set for 64-bit OS. Use %PROGRAMFILES%\CWRSYNC on 32-bit.
- change the rync line to match the local backup location (C:\Backup) and the rsync module
Add a scheduled task to execute the batch file once a day. The rsync server rotates files at 4:02 AM, so run the batch file anytime after that (eg. 5:00 AM). Once the script is complete, the database will be backed up on the database server and the rsync server, where backups as old as a week can be retrieved if necessary.
If the backup file is quite large it can be compressed on the database server prior to running rsync. Install gzip for Windows and add the following to the batch file:
then add <database.bak.gz> to /etc/logrotate.d/db-rsync.
