It is often the case that people don’t worry about backups until disaster strikes and they actually need one. In some smaller intuitions and schools, where one overstretched IT person wears many hats, there isn’t a dedicated Database Administrator and this essential IT task can be ignored. This article will go through the basics of how you can setup a solid backup plan for Microsoft SQL Server in under an hour. This guide doesn’t require any advanced DBA experience with two location redundancy for free or a low cost for more advanced options.
For this example we will be using the free tool SQL Backup and FTP which offers an easy way to push your database backup to an off-site location (network/FTP) or even to the cloud.
Preparation: Download and install the software. That is the latest version of the Freeware, which can be upgraded any time with a license key.
Step 1: Open the software and click on the “Connect to SQL Server” button. This will search for MS SQL instances on the server. If you don’t use Windows Authentication you will need to enter a valid SQL user account and then click Test and Save the SQL Connection.
Step 2: A list of databases for the SQL Connection you provided will show up. Simply check off the ones that you would like to backup. The freeware only allows two per server, if you need more you can upgrade the license.
Step 3: Chose the location where you would like to push the backup files to. Best practice would be to store the files locally as well as an off-site location. Here you could select “Store backups in a Local/Network folder” and also select the “Send Backups to FTP Server.”
Optional Cloud Storage: If you would like to store the database locally as well as in the cloud in one simple step all you need to do is setup a Cloud Storage account (free ones such as Dropbox or Skydrive are easy to install) on the server. Then when you select the “Network/Storage” location simply select the shared local folder of the cloud synced storage location. If you choose this option it is recommended to choose the AES encryption option under “Advanced Settings” for security.
Step 4: Schedule the backup job. To do this click the “Schedule this job” checkbox and then the “Advanced Schedule” button. The simplest way to do this is choose the “Full Backup” every 24 hours at a time when system usage is low, such as early in the morning. Leave the Windows Task Scheduler (default) radio button clicked.
Every method had a trade off, but the Full Backup is the easiest and will assure that you can actually restore a complete database for disaster recovery. If disk space becomes an issue you can adjust how long before older backups auto delete or reduce the frequency the jobs run on this software.
Optional Failure Warning Step: It is a good idea to click the “Send e-mail confirmations” On failure e-mail to an address that you check regularly.
Step 5: Now you can just click the Save button and then “Run Now” to execute the first job. After you exit the software it will continue to run the backup automatically at the frequency you selected on Step 4.
If you would like SimpleApply to setup a comprehensive database backup plan at a low cost please Contact Us.