SQL Server Typical Maintenance Plans: Automated Database Backups

December 4th, 2015

There are a lot of posts appealing to one simple truth – need perform backups on a regular basis. People will always be divided into two categories: those who do backup, and those who don’t. The first category, which ignores this advice, can often be found on relevant forums with much the same questions:

– My discs failed/someone has deleted my db… How can I restore my data?
– Do you have the latest backup?
– No.

In order to avoid this unpleasant situation, minimum effort is required. First thing you should do is select a disk array for storing backup copies. Since storing backups together with database files is not our choice, the second step will be to create a maintenance plan for the database backup.

Next, we will discuss some subtleties associated with backups.

Let us create a table for recording backup-related error messages:

The following script may be used for every day database backup:

If the Database Mail component is configured on a server, an email notification about the problems, which may arise, can be added to the script:

At this stage, the working script for automatic backup creation is ready for use. The only thing left to do is to create the job, which would run this script on schedule.

The owners of the Express editions should to be given a special mention, because SQL Server Express edition does not provide the possibility to use SQL Server Agent. As sad as it sounds, in fact, that can be easily solved. The easiest way is to create a bat file with a similar content:

Next, open the Task Scheduler and create a new task therein.

Task Scheduler

The second option is to use a third-party software, which allows to run the tasks on schedule. Among them is SQL Scheduler – a convenient free tool.

Now let’s talk about useful things associated with backups.

Backup Compression

The backup’s compression feature appeared: SQL Server 2008 (only Enterprise/Developer edition), 2008R2/2012/2014 (only Enterprise, Business Intelligence, Standard, Developer edition). There was a time when working with SQL Server 2005, I had to compress backups using 7Zip. Now everything is much easier.

We should bear in mind that the backup compression will only be used when you execute the BACKUP command with the COMPRESSION parameter or enable a default compression using the following command:

By the way, the compressed backups have a number of advantages:
1) you need less space to store them;
2) restoring databases from compressed backups usually takes less time;
3) the creation of compressed backups is faster and requires fewer I/O operations.

A few drawbacks also exist – when working with compressed backups, the CPU load increases.

The following query returns the size of the last FULL backup both compressed and not compressed:

Usually, the compression reaches 40-90%, leaving aside the binary data:

If you modify the previous query, you can monitor the databases, for which backup copies were created:

If you use SQL Server 2005, than this line:

should be changed to:

The results of this query can help prevent many of the problems:

You can see right away whether all the databases have FULL backups for the current date.

Then you can look at the time of backup creation. You may ask why? Suppose that the backup of the DB_Dev database used to take 5 seconds, and then it began to last for 1 hour. The reasons for that can be various: disks do not handle the load, the data in the database grew up enormously, a disc failure in RAID and the writing speed has decreased.

If the database contains the FULL or BULK_LOGGED recovery model, it is desirable to make log backup from time to time, to relief the server from constant growth of the LDF file. You can see the degree of filling of the data file and log for the database using this query:

The results of the query on my local SQL Server Instance:

There are also a couple of interesting tricks that can make your life easier. Specify several paths during the execution of the BACKUP command, and the resulting backup file will be cut into pieces of about the same size:

This can be useful when you copy a backup to a USB flash drive with the file system FAT32, which has the limitation on the maximum file size.

Another interesting possibility is the creation of a backup copy (only in Enterprise/Developer edition). Instead of creating a backup in a default folder and manually copying the script to a shared folder, you can simply use the following command:

Leave a Comment