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:
- You’ll start by connecting to the correct SQL server instance using SSMS.
- The next step is to expand the server tree in the “Object Explorer” window.
- In the “Databases” folder you’ll find your database that you want to create a backup of.
- 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.
- 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.
- 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:
- In the “Back Up Database” window you’ll have to choose “disk” in the drop down menu at the “Back up to:” line.
- 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.
- 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).
- 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:
- At the “Back up to:” line select “URL” instead of “disk”, and click the “Add…” button.
- 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.
- Signing in to your Microsoft account is a requirement and can be performed in the “Connect to a Microsoft Subscription” dialog window.
- 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.
- Clicking “OK” should close the “Connect to a Microsoft Subscription” window and bring you back to the “Select a backup destination” dialog box.
- 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:
- Navigate to the “Jobs” folder and right-click it. In the following menu choose the “New Job…” option.
- In the next dialog box you’ll have the ability to specify the name of your job.
- The majority of job customization can be done via SQL statements. Here’s one that creates a new backup step (under the “Steps” tab):
BACKUP DATABASE [CurrencyExchange]
TO DISK = N'G:\DatabaseBackups\CE.bak'
- SQL statements can also specify the backup type for the job:
BACKUP DATABASE [CurrencyExchange]
TO DISK = N'G:\DatabaseBackups\CE.bak'
- ...transaction log backups can also be done with the following statement:
BACKUP LOG [CurrencyExchange]
TO DISK = N'F:\TLogBackups\CE.log';
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).
- 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.
- 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.
- The “Job Schedule” tab allows you to specify the frequency and the starting date of your job.
- 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:
- 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.
- Select “Back Up Database Task”, and click on the newly created plan to configure it.
- Scheduling options can be changed using the “Sub plan scheduling” category.
- 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:
- Right under the “New Maintenance Plan…” option you’ll find the “Maintenance Plan Wizard” option to start up the process.
- “Select Plan Properties” tab allows you to change the name of the job, the schedule of this job, and some other things.
- 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.
- The last configurable step is all about task configuration, like backup integrity verification, target databases, sub-directories for each different database, etc.
- 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.
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.
SQL Database Backup Configuration with Bacula
1. From the BWeb configuration page, click 'Add a new Backup'
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.
3. Next, the client on which the SQL database resides is selected from the client drop-down list.
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.
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 any final options are configured, 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 are available in the MSSQL-VDI Plugin documentation.