Creating and storing database backup

Regular backups are a sure way to save your important data. Anything can happen: from a fire in the data center to just a carelessly written and running script in your database. To this must be prepared. Although the probability of this is quite small, but the losses that can be incurred are enormous. Therefore, the weight of this node in the decision tree will be significant, and therefore, you need to take care of backups as soon as possible.
In this article we will describe how the collection and storage of backups in our system is organized.


')

General approach

Our backup storage scheme is as follows:
- Every day at midnight we make a full backup of the base;
- every day at noon we make differential backups;
- every 30 minutes we make backup transaction log.

Thus, theoretically, in the worst case, Workzilla could lose data in 30 minutes, and on average in 15 minutes. Which is very unpleasant, but still better than losing more or everything. Of course, there are more reliable schemes, but what we use, I think, will suit many.
We make backups on one of the specially designed disks and upload data to Amazon S3, which guarantees that the data will not be lost.

Automatic backups in MS SQL Server

MS SQL Server makes it easy to organize a backup scheme that fits your system. We use the “Maintanance Plans” for this. A new plan can be created using the built-in assistant or an intuitive plan designer. More information about the creation of "Service Plans" can be found here .
In addition to creating the backup itself, you can run additional scripts to clean up the database, check it for data integrity, delete old backups so that the disk does not overflow, etc.
An example of a ready plan for creating full backups is shown in the picture.

"

After the plan is created, you need to configure the appropriate work in the SQL Server Agent.
To do this, make sure that the service is installed and running. In the object inspector window, select SQL Server Agent -> Jobs -> right-click on your work and select Modify from the context menu.



In the window that opens, you can view all the information about your work and change it if necessary. Below is an example of a form for changing the schedule by which the selected work will be launched.

Download to Amazon S3

Now backup copies of your database will be created according to the scenario that you described. In the next step, it is important to take care that the backups are not lost, if something happens to your server, for example, the hard disk gets corrupted. It’s safest to copy backup to another location. This could be a server in another data center, some cloud storage or something else.
We use Amazon S3 for this. It guarantees that the data will not be lost, besides, this service is quite cheap.
To synchronize the disk with our backups and S3, we use a simple samopisny utility in python. It is launched using Task Scheduler, a built-in Windows utility. The script runs every 30 minutes and, due to the small amount of backups of the transaction log, this procedure does not consume a lot of resources.
If you decide to use Amazon S3, then you should remember about the 5 GB limit when downloading a file. If you need to upload larger files, you will have to upload files in parts.

Do I need compression?

File sizes of backup copies of databases can have substantial volumes, and several problems can occur when uploading to another server:
- outgoing traffic increases dramatically;
- the network channel is clogged;
- the number of I / O operations on the current disk increases.

Backups can be compressed using various archivers (we recommend 7z).
Thus, the problems described above are solved, but another one arises: a significant amount of CPU time is required to compress the file.

You have to decide which option is preferable for you. In the case of Workszilla, we do not compress backups.

What you need to remember

We recommend setting up a system that will periodically check whether all your backup processes are working correctly. We at Workzill use Nagios. It is better to configure it on a separate server inside the local network. From what needs to be checked, I highlight the following:
- whether SQL Agent is running;
- whether new files appear on S3;
- time to create the latest backups;
- Is there enough disk space with backups?

For those who are very concerned about the security of their data, you can archive backups with a password. Now, if backup somehow falls into the hands of intruders, it will be problematic for them to get data.

If backup will be used in the future only by developers, then it is recommended to delete / replace critical data. We do this with password hashes, emails and other personal information of users.

Again, having backups is very important. But we hope that they will not be useful to you.