How to Backup SQL Server? SQL Server Backup Software Tools & Solutions.

SQL has become the industry standard for manipulating and querying data in a relational database. Correspondingly, one critical area of any backup plan, including many effective disaster recovery strategies, 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. This page discusses the main types of backup and some general backup best practices. It then demonstrates it in actual 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 level – the only rule is to backup literally everything, from regular data to system tables, objects and so on. These backups will 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 backups, to minimize and help 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 very large in size. The difference between this backup and the full one is that the former 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 level, 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 potentially allows for a great reduction in the amount of data lost if some sort of data breach or data loss happens all of a sudden.

• 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.

Different methods to create SQL server backup

There’s a number of ways to perform SQL backup and restore by using a number of tools, from the built-in general management software like SSMS (SQL Server Management Studio), or some specific parts of it, like SQL Server Agent, or even the most basic SQL backup tool – the command line.

First of all, SSMS is a relatively powerful software that can do a lot of SQL server-related operations like administration, configuration and management of different SQL server components. Amongst the vast variety of SSMS capabilities there’s also the ability to backup database SQL server.

We can discern three main backup types that can be created with the help of a SQL Server Management Studio – SQL server backup and restore to the default location, backup SQL database to the custom location and backups to an already existing Azure Blob storage service container.

The first backup type is most likely the easiest one to perform:

1. You’ll start by connecting to the correct SQL server instance using SSMS.
2. The next step is to expand the server tree in the “Object Explorer” window.
3. In the “Databases” folder you’ll find your database that you want to create a backup of.
4. Right-clicking on said database brings you a number of options, you should hover over the “Tasks” one, and choose the “Back Up…” option in the second part of the menu.
5. Since the backup is performed to the default location – there’s no need to change anything in the “Back Up Database” window, so you can just start the backup process with the “OK” button.
6. After the backup process is complete, clicking “OK” in the status window allows you to finish the entire backup process.

Bear in mind that this will be a full backup, and it might take a substantial amount of time to perform, depending on the total server size.

Performing a backup database SQL with a custom path is slightly different from the process above, the only difference begins after the first 4 steps are done. So the process continues with step 5:

1. In the “Back Up Database” window you’ll have to choose “disk” in the drop down menu at the “Back up to:” line.
2. You’ll also have to remove all of the existing backup paths using the “Remove” button until there’s nothing left, and then you can use the “Add…” button to get to the “Select Backup Destination” window.
3. Specifying the target backup location’s file path and file name is the next step (using .bak as the target file extension is also recommended to simplify the classification).
4. Clicking “OK” button in this window and back in the “Back Up Database” one would start up the backup process, and clicking “OK” once more after that allows you to close the SSMS backup status box.

Speaking of custom storage locations, it’s also possible to create a backup SQL database and save it on the Azure Blob container. The process is the same for the first four steps, and the main difference is, again, in the “Back Up Database” window:

1. At the “Back up to:” line select “URL” instead of “disk”, and click the “Add…” button.
2. Click “New container” if you haven’t yet registered an Azure storage container, or choose the existing one if you have it in the list already.
3. Signing in to your Microsoft account is a requirement and can be performed in the “Connect to a Microsoft Subscription” dialog window.
4. In that same dialog box you’ll have the ability to select the storage account, select the blob container that’ll serve as the storage, and select the expiration date for the shared access policy you’re creating. The creation process can be performed via the “Create Credential” button.
5. Clicking “OK” should close the “Connect to a Microsoft Subscription” window and bring you back to the “Select a backup destination” dialog box.
6. Modify the name of the backup file if you want, and click the “OK” button once again to close this window. Backup itself can be initiated by clicking the “OK” button once more – at the bottom of the “Back Up Database” window.

While SSMS is more about performing backups manually, applications like SQL Server Agent are more about jobs and scheduling specifically, which is understandably a bit more complex than creating each backup by yourself.

SQL Server Agent also has two different ways of creating/scheduling backups – using different jobs, and using server maintenance plans.

To use jobs as a part of the SQL server backup and restore process, there’s a specific pattern that you have to follow:

1. Navigate to the “Jobs” folder and right-click it. In the following menu choose the “New Job…” option.
2. In the next dialog box you’ll have the ability to specify the name of your job.
3. The majority of job customization can be done via SQL statements. Here’s one that creates a new backup step (under the “Steps” tab):

USE CurrencyExchange

GO

BACKUP DATABASE [CurrencyExchange]

TO  DISK = N’G:\DatabaseBackups\CE.bak’

WITH CHECKSUM;

4. SQL statements can also specify the backup type for the job:

BACKUP DATABASE [CurrencyExchange]

   TO  DISK = N’G:\DatabaseBackups\CE.bak’

   WITH DIFFERENTIAL;

   WITH CHECKSUM; 

GO

5. …transaction log backups can also be done with the following statement:

BACKUP LOG [CurrencyExchange]

   TO  DISK = N’F:\TLogBackups\CE.log’;

GO

There’s a few things to mention here. First of all, the backup type specified above is differential, and an absolute requirement for this backup level is for the ‘Full’ backup to exist beforehand.

The second point concerns different backup types in general – even if it’s just Full, Differential and Transaction Log. By spacing out these backups you’ll be able to keep your database safe and ready for the restoration at any point in time with little to no data loss (if you’re doing, for example, a full backup every 24 hours, a differential backup every 6 hours, and the transaction log twice an hour).

6. After deciding the right scheduling settings, you can finish that step up by clicking “OK” to add a step and “OK” again to create the job in the first place.
7. As soon as you’re done with creating the job, you can start scheduling it. In the “Schedule” tab click the “New” option in the “Job” dialog.
8. The “Job Schedule” tab allows you to specify the frequency and the starting date of your job.
9. Select “OK” to finish the scheduling setup and use the “Start Job at Step…” option to start your new job.

Another way of working with SQL Server Agent is tied with the Server Maintenance Plan capabilities. This can also be done in two different ways: creating the scheduled backup manually or using the maintenance plan wizard.

While the usage of maintenance plan wizard is somewhat easier, we’ll start with the manual scheduled backup plan creation:

1. The “Maintenance Plan” folder is under the “Management” node, right-clicking it allows you to choose the “New Maintenance Plan…” option to start the process.
2. Select “Back Up Database Task”, and click on the newly created plan to configure it.
3. Scheduling options can be changed using the “Sub plan scheduling” category.
4. After ending the configuration process you can finish up the process by clicking “Save”, this would create the backup job that you want.

The Maintenance plan wizard is rather easier than that, but it’s also less customizable, as well:

1. Right under the “New Maintenance Plan…” option you’ll find the “Maintenance Plan Wizard” option to start up the process.
2. “Select Plan Properties” tab allows you to change the name of the job, the schedule of this job, and some other things.
3. The next step includes a number of options that you can check or un-check if you want, including the backup type (full, differential, transaction log), check database integrity, clean up history, and others.
4. The last configurable step is all about task configuration, like backup integrity verification, target databases, sub-directories for each different database, etc.
5. Before starting up the creation process you’ll be able to overview the entire task in one window. After confirming everything, click “Finish”, and the creation process would begin.

The maintenance plans might seem highly convenient, but they’re quite limited, as well. They are suitable for less experienced users, but the ever-growing server complexity would likely lead to a huge mass of different backup tasks that would be hard to manage.

There’s also another alternative when it comes to SQL server backup creation as a part of the SQL backup and restore process – using nothing but the command line. The proper sequence of commands allows you to extract a database from under a Windows Administrator:

SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak’”

You’re free to change the location of the backup (after the “TO DISK=’), and the target database is named “MyDB” in this case.

Useful SQL Server backup practices

• SQL backup schedules

Don’t think that you are done with backups after you’ve performed a successful backup once. Therefore, the ability to replicate all these tasks again without having to use manual commands for every backup is very important. 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 scenario.

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

Don’t forget about how your business works with data, as well. For example, an IT company would likely want their data backed up as often as possible, but that is perhaps 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 recover your data from it. There are a lot of different business requirements that your backups have to meet, and the more often you check the data, the less chance of some of it being corrupted when you need it the most.

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 somewhere other than your main working site is because an event that can do damage to your main databases and servers might also damage your backup drives and backups in general. 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 taken away for off-site storage.

It’s important to mention that without specific 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 in itself move any of the previous backups there – the system would only be saving new backups in that particular place.

Third-party SQL Server backup software solutions

Like any other database type, SQL Server is a database that can often contain a lot of valuable information which needs to be secured and protected at all times, which includes not only encrypting said data but also creating backups of it and storing them in multiple locations. There is quite a large choice of third-party SQL Server software for backup and recovery, and we showcase several different examples in the list below.

Handy Backup Server Network

Handy Backup is a relatively new backup solution on the market that has already earned its name among some of the most popular backup software offerings. Its Server Backup Network solution is a great choice for SQL Server databases, offering a plethora of different operations that can be automated. It is capable of creating backups of Hyper-V images, as well as VirtualBox and VMware environments. Other capabilities of Handy Backup include the capability to back up cloud accounts, FTP storage servers, websites, email content, and more. Its biggest concern by far is a rather long and arduous initial setup process, which has been reported as time-consuming and difficult by some users.

IDERA SQL Safe Backup

IDERA SQL Safe Backup is a rather noteworthy solution for very specific backup targets such as Amazon S3, Azure Blob Storage, and, of course, SQL Server databases. It is a comprehensive backup and recovery solution for SQL Server that provides granular restore capabilities, as well as event-driven or schedule-driven comprehensive backup tasks. IDERA SQL Safe Backup can reduce backup storage size, reduce backup/recovery time, while also providing various SQL management features, making it one of the best SQL Server backup software on the market.

SQL Backup

A rather large database software provider RedGate also has its own take on a SQL Server backup solution – with a rather simple title “SQL Backup”. It is a reliable and effective server backup solution with a command line and a GUI that can perform SQL Server database backups and save them either to the cloud storage of your choice, or other storage mediums such as disk or tape. It offers integrity checks, data compression, and data encryption for all of its backups, as well as a number of other useful features.

SQLBackupAndFTP

This SQL Server backup solution may not look like much, but its unusual name is also one of the reasons it is so easy to remember. SQLBackupAndFTP is a multifunctional server backup software that offers a multitude of features combined with the capability to cover SQL Server, Azure SQL, PostgreSQL, Amazon RDS SQL, and MySQL databases. It can perform several different backup types (differential, full, incremental), it can create backups of transaction logs, and backups themselves can be stored either in the cloud, on a network, or on an FTP server. It is a simple but effective solution that works best for smaller businesses and may not be as suitable for large enterprises.

EaseUS Todo Backup

EaseUS Todo Backup is a versatile backup and recovery solution that suits a variety of different use cases – be it cloud drives, virtual machines, databases, and so on. It is one of the best SQL Server backup software on the market, offering drive cloning, web-based management via a console and the capability to be deployed either on-premise or in the cloud. It also works with a variety of storage media types, from local storage and external hard drives to cloud storage, NAS, and more. The solution’s backup capabilities are also rather vast, offering three different backup types – incremental, differential, full – as well as many customization options.

SQL Server Backup

SQL Server Backup is the name of the solution provided by databk.com, which is a very simple name that makes it easier to find on the Internet. It is a backup and recovery solution that covers several different versions of SQL Server, offering multiple ways to manage your SQL Server backups. It also has the capability to either troubleshoot or repair SQL Server databases that have been corrupted in one way or another. Its interface may look rather dated, but it still holds up quite well in terms of its SQL Server backup solution capabilities.

UltraBac

UltraBac is perhaps somewhat dated, but nevertheless still a powerful SQL Server backup solution that offers quite a lot of uncommon features when it comes to performing database backups as a whole. It has a universal boot media builder, bare-metal restore, dissimilar hardware restore, and many other features. It claims to be able to return an average machine to fully working in about 15 minutes. It supports physical-to-virtual disaster recovery methods, it has file-level backups to SAN, NAS, tape, and more. However, its biggest issue is that it does not offer any cloud support as your backup storage space, which means that cloud storage providers such as OneDrive, Dropbox, and Google Drive would not be able to receive your backups automatically when using this SQL Server backup tool.

Iperius Backup

Iperius Backup is one of many SQL Server backup tools in the field, but its versatility is what makes it special. It can create backups of VMware ESXi, Hyper-V, drive images, MS Exchange, SQL Server data, and more. These backups can then be stored in FTP, NAS, cloud, physical storage, and so on. Data itself is thoroughly encrypted and protected from ransomware, and the solution itself supports VSS, recovery drive creation, hard disk cloning, as well as many other useful features. This solution would be great for companies that already have multiple different environment types, so they need a solution that covers everything at the same time to avoid fragmentation and other similar issues.

LiteSpeed for SQL Server

LiteSpeed for SQL Server is one of the few SQL Server backup tools on the market that have a free version available. It is a reliable SQL Server backup tool that can create backups of SQL Server databases in multiple locations – cloud storage, network shares, local drives, and so on. It offers a lot of backup and recovery options for SQL Servers, supports every SQL Server version from 2005 to 2019, and can be configured to send backups to and restore them from one of many cloud storage providers, such as Google, Amazon, Microsoft, IBM, etc.

Acronis Cyber Backup Advanced

Acronis is another example of a notable data protection software provider that has the capability to work with SQL Server databases. Acronis Cyber Backup Advanced is a highly effective solution for SQL Server backup/recovery operations, be it in physical storage, virtual storage, or even in the cloud. It can offer disk image backup capabilities, a universal restore system, anti-ransomware capabilities, scheduling, image-level backups, and more. It also has a rather high recovery speed, allowing for quick database restorations whenever necessary.
There is also another solution worth noting here – Bacula Enterprise. This is an especially highly secure, customizable and flexible solution that has the broad compatibility and integration features often required by SQL Server users. It also features non capacity-based licensing, a boon to large storage environments and larger organizations. It has many different features as well as the ability to extend its functionality with the help of its own “module” system. One such module allows it to perform various SQL Server-related operations, such as backups, recovery processes, and more.

SQL Server backup solution from Bacula

Bacula is especially adept at backing up databases – Its 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 databases and recover critical database items to a point in time remains a necessary part of an effective disaster recovery, business continuity, and security strategy.

Bacula Enterprise provides the ability to backup many mission-critical databases, including Oracle, MSSQL, SAP HANA, MySQL, and Postgresql, while delivering the important point in time and offline recovery options that are needed.

The guide that follows will show you how to backup SQL Server databases using Microsoft’s Linux virtual device interface.