Home > Backup and Recovery Strategies Blog > How to Backup SQL Server Database?
1 Star2 Stars3 Stars4 Stars5 Stars
(28 votes, average: 5.00 out of 5)

How to Backup SQL Server Database?

  • November 20, 2018, Rob Morrison

How To Backup SQL Database? Bacula Systems Technical Overview.

SQL has become the industry standard for manipulating and querying data in a relational database. Correspondingly, one critical area of any backup plan, including successful disaster recovery strategies in general, are safe, effective and fast SQL database backup - and even more importantly - recovery. Correct implementation and policies are needed in order for it to work reliably, and that part is the main focus of this article. In this we’ll go over the main types of backup, some general backup practices and then demonstrate it in practice, by also explaining how to set up your SQL database backup with Bacula.

SQL database backup types

  • Full backup

Full backup of SQL Server is probably the simplest backup type - the only rule is to backup literally everything, from the regular data to system tables, objects and so on. These backups are used to restore your entire system to the state it was at the moment of the last backup. Transaction log truncation isn’t done while performing full backups, but it’s advised to consider using transaction logs backup together with full backup to minimize and identify the amount of data that may be lost in a disaster scenario.

  • Transaction log backup

This is somewhat different, since transaction log aims to backup everything that happened, database-wise, since the last log backup happened, completed with truncation. It is used to capture all of the interactions within the database, including both DML (Data Manipulation Language) and DDL (Data Definition Language). Transaction log backup allows you to restore your database in almost any point of time you need, including right before a disaster that caused data loss or corruption.

  • File-group / File backup

This one works the best with databases that are massive in size. The difference with the full backup is that it stores all of the data in files / file groups (in case there are more than one). This particular type also requires the transaction log to be backed up and ready to successfully restore all of the data required from this particular backup.

  • Differential backup

As a complementary backup type, the point of differential backup of SQL Server is to maintain the entire operation sequence of your database without storing the same data multiple times. A typical beginning point of a differential backup is the last full backup done with the database. While it is only useful when used together with full backup, it allows to greatly lessen the amount of data lost in case of a disaster if your RPO and RTO are not small enough.

  • Copy-only backup

Copy-only backup might be considered a reserve inside of a reserve, since most of the time it is done on an ad-hoc basis, without disturbing the existing database backup process. It is exactly the same as a full backup, with one significant difference - transaction log copy-backup would completely ignore this backup type. That’s why both differential and transaction log backups are not used with this particular backup type.

Useful SQL Server backup practices

SQL backup schedules

Don’t think that you are done with backups once you’ve performed a successful backup once. Even more important than that would be the ability to replicate all of that again without your manual commands for every backup. That’s where scheduling comes in. It is used to both lessen your workload by not making you perform every backup by yourself and to also ensure that your data is safe in case of a disaster.

When setting up your scheduled SQL Server backups, it’s time for you to choose the time between backups. Keep in mind that you’ll have to choose between doing backups frequently and paying the increased costs of additional storage space but losing less data in case of a disaster, and doing backups less often, paying less for storage but losing more data in case something happens. 

Don’t forget about how your business works with data, as well. For example, if you’re an IT company would want their data backed up as often as possible, but that’s not a requirement for other companies that don’t have huge amounts of data changing all the time.

SQL backup restore testing

SQL backups also should be verified from time to time since a backup is useless for you if you can’t get any data out of it. There are a lot of different business requirements that your backups have to meet, and the more often you check them - the less the chance of one of them corrupting when you need it the least.

A lot of variables are included in backup testing, like data loss windows, data loss threshold, environment, recovery timeframe and more.

Geographical location

The reason for keeping at least one backup copy away from your main working site is because something that can do damage to your main databases and servers might also do the same for your backup drives and backups in general, like a natural disaster or such.  That’s why it is highly advised to use a remote facility to store at least one of your backups, be it via cloud or by any other means, such as tape.

It’s important to mention that without correct commands SQL database backups would be saved in the default storage, which might be exactly what you don’t want to do. There’s also the fact that changing your backup location would not move any of the previous backups there - the system would only be saving new backups in that particular place.

Bacula is especially adept at backup up databases - Bacula’s architecture means that any SQL database engine can be used, so SQL databases can be backed up with reliability and speed. As a result, when it comes to speed, efficiency and simplicity, Bacula is as powerful - or more powerful than any other backup and recovery solution for SQL.

Enterprise IT is constantly evolving, bringing new trends and new ways of approaching the delivery of critical services and data. From virtualization to cloud, from containers to converged infrastructure, one thing has remained constant. Database servers remain a key component of most organizations, and often contain the most critical information for the continuity of operations. In the end, no matter how virtualized and highly available the infrastructure, being able to backup database and recover critical database items to a point in time remains a necessary part of a fully realized disaster recovery, business continuity, and security strategy.

Bacula Enterprise Edition provides the ability to backup many mission-critical databases, including Oracle, MSSQL, SAP HANA, MySQL, and Postgresql, providing the much-needed point in time, offline recovery option that is there when it is needed most. The guide that follows will show you how to backup SQL Server database using Microsoft's Linux virtual device interface.

SQL Database Backup Configuration with Bacula

1. From the Bweb configuration page, click 'Add a new Backup'

backup sql database

2. This brings up the wizard to add a new SQL database backup job. First give the job a name and description. It's also possible here to select common defaults with other backup jobs, called JobDefs in Bacula, if appropriate. Ideally the job name will match the hostname along with a descriptor for the service being backed up on that host, in order to make log parsing easier.

sql server backup

3. Next, the client on which the SQL database resides is selected from the client drop-down list.

sql backup database

4. After choosing the client, BWeb will ask what SQL database is to be backed up. This is known as the Fileset in Bacula. There is no existing template for this particular type of SQL backup, so it will have to be created by clicking the 'now' link in the description.

backup sql database

5. The new fileset will need a name, and in this case the name is specific to this backup job. The same fileset can also be shared among many SQL database backup jobs that share the same parameters. After giving the fileset a name, clicking on "Add include list" brings up the next configuration window where the data to be backed up is configured.

6. Choose the client, and BWeb will connect and display the filesystem available for backup. Rather than backing up files in this example, the configuration is of a plugin to back up a database. Clicking "Edit Plugins" brings up the plugin list.

7. From this list the SQL database backup plugin is chosen, bringing up the configuration screen.

8. On the configuration screen are the various options available to the SQL Server backup plugin. By default, the plugin will authenticate via the local service account under which the Bacula File Daemon is running on the SQL server (see configuration screenshot for one possible way to permit Bacula to access the databases), and then back up all databases on the server. The options in the list allow the plugin to be configured to use alternative authentication, backup only specific databases or exclude databases matching a pattern, as well as other options. Please refer to the Bacula Enterprise SQL database backup plugin whitepaper for more details about all the configuration options available. In this example, the defaults are used to back up all databases on the server.

9. Finally, the plugin is added to the fileset and configure any final options, such as enabling deduplication for any job that uses this fileset. The Bacula Enterprise main manual covers each of the available configuration options in detail, and Bacula Systems support can assist in optimizing fileset options.

10. After applying changes and saving the fileset, a schedule must be chosen from the available list of configured backup schedules, or a new schedule can be created. If no schedule is chosen, the job will only run manually.

11. Finally, the job is ready to be saved. Any advanced options can be changed, added, or modified now. For the example here, no additional changes need to be made and the job can be saved, and then committed and the director must be reloaded. Reloading the director configuration does not affect running jobs.

12. The new job is ready to go. A manual run will verify that everything is set up as desired.

13. After a manual run of the job, a review of the job log shows the databases and related files backed up, any errors or warnings, and the total job statistics and Bacula status. In this case the SQL backup job is OK, so the configuration task here is complete.

Final notes on how to backup SQL database:

  • This is a basic setup guide; please refer to Bacula Enterprise documentation and support for more details and advanced configuration options
  • Verifying and documenting the restore process is an important next step.
  • Configuration instructions using text editor and Bacula configuration files is available in the MSSQL-VDI Plugin documentation.
About the author

Rob Morrison

Rob Morrison is the marketing director at Bacula Systems. He started his IT marketing career with Silicon Graphics in Switzerland, performing strongly in various marketing management roles for almost 10 years. In the next 10 years Rob also held various marketing management positions in JBoss, Red Hat and Pentaho ensuring market share growth for these well-known companies. He is a graduate of Plymouth University and holds an Honours Digital Media and Communications degree, and completed an Overseas Studies Program.

  1. Martin

    Are you looking for an ultimate guide to backup your SQL database? This step-by-step tutorial will help you.

  2. Allison

    This is a great post with detailed guide that shows how to backup database (SQL) using Microsoft’s Linux virtual device interface. Also Bacula provides the ability to backup other mission-critical databases such as Oracle, MSSQL, SAP HANA, MySQL, and more.

  3. Artur

    I’m looking a documentation to setup Bacula to work with MSSQL AlwaysOn . Do you have it ?

  4. Adam

    This post can help users to learn how to backup sql database. Here is a full tutorial with pics so you’ll deal with sql backup without troubles. I have done sql backup database successfully using this post.
    If you still need more information about sql server backup just look through Bacula documentation or contact support.

  5. Emilia

    I believe that this tutorial of sql backup database is the best. I have searched such informative sql backup guide for a long time.
    I used it for my sql server backup too, and I met success. I didn’t need any documentation or support, I just followed the steps. And you can do backup sql database painless too, just check this post.

  6. Andrei Yunisov
    Andrei Yunisov

    @ Adam, we do support MSSQL Always-on clusters. How to set it up will require our plugins and working with our docs and support team.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>