Home -> Corporate Data Backup -> Enterprise Data Backup Tools -> SQL backup software and tools

SQL Server backup tools from Bacula

Perform fast and convenient backups and restores with our MS SQL Server Backup Tools. 

Among its vast range of features, Bacula Enterprise Edition also backs up your Windows data with an impressive combination of ease and functionality.

This includes the ability to perform fast and convenient backup using our MS SQL backup tool. Now you can precisely backup very large amounts of specific Windows-based data in your SQL Server at a fraction of competitors’ costs – and achieve Point In Time SQL Server restore.

sql server backup tool

More Options – More Sophisticated SQL Backup Software

Bacula Systems’ SQL backup software delivers exceptional performance via a modern highly reliable architecture and offers you the following SQL Server backup tool capabilities:

  • Full, Incremental and differential SQL Server backups
  • Database-level SQL backup
  • Point in time recovery (PITR)
  • Ability to include/exclude databases from the backup job
  • Support for “Copy Only” backups
  • Restore MS SQL backup files to disk
  • Send backup stream directly to the Storage Daemon
  • Checksum plugin command and restore option to ask the SQL Server to verify the checksum of the data
#
SQL Server backup tools — independent of the VSS engine

The MS SQL backup software from Bacula does not use VSS snapshots to perform the backup, so it is possible to specify “Enable VSS = no” in the Bacula FileSet. This MSSQL server backup solution achieves advanced backup and restore capabilities via the Microsoft API for SQL Server. Therefore we also support the security mechanisms and authentication types that come with Microsoft SQL Server.

 

#
SQL Server Backup Encryption and Compression

With Bacula Enterprise Edition’s SQL Server backup compression capabilities you can enjoy the efficient backup with compression and hence – with much less network bandwidth utilization. Bacula’s security technologies include EFS or TLS encryption which allows you to send MS SQL backups securely from file to storage daemon.

#
Transaction Log Backup and PITR

Bacula Enterprise Edition allows you to restore MSSQL set of data or a particular setting from a very precise time in the past. With full or bulk-logged recovery models you will be able to do PITR or use LSN information to restore to the exact state that you want to have. You can restore to exactly the required state of data base at a very specific point in time – even down to an exact second. From a Transaction log backup, a restore will compose the database state from a variety of chosen SQL Server backup types.

 

#
Backup Automation

With Bacula Enterprise Edition’s scheduling capabilities and scripting features you’re able to implement SQL Server backup automation and reduce time to manage your backups. Backup jobs for your SQL Server can be launched using scripting logic and scheduler.

SQL Backup Software Options 

Full Backup

The Full backup saves the database files and transaction log to provide complete protection against media failure. If one or more data files are damaged, media recovery can restore all committed transactions. In-process transactions are rolled back. The master and the mbdb databases are always backed up in this mode.

Differential Backup

A differential backup is based on the most recent, previous full database backup. A differential backup captures only the data that has changed since that full backup. When using the Differential backup feature, the backup chain is very critical. If for some reason, the Full backup used as referenced by MS SQL is not available, the Differential data will not be usable. The SQL backup and restore tools from Bacula use different techniques to avoid this problem, so if a problem is detected, the Differential database backup might be automatically upgraded to a Full backup.

Transaction Log Backup

The “Transaction Log Backup” MS SQL feature is implemented as the “Incremental” level with Bacula. The database must be configured with the full recovery model or bulk-logged recovery model. If the database uses the simple recovery model, the transaction log file will be truncated after each checkpoint and the backup of the transaction log will not allow Point in Time Recovery. The full restore will be possible, but not the restore to a point in time.

MS SQL Database Configuration

The master database must be backed up. If master is damaged in some way, for example because of media failure, an instance of MS SQL may not be able to start. In this event, it is necessary to rebuild master, and then restore the database from a backup. Only full database backups of master can be created.

SQL Server Restore

You can use all the regular ways to start a restore. However, you must make sure that if restoring differential data, the previous full backup is also restored. This happens automatically if you start the restore, in bconsole, using the restore options 5 or 12. In the file tree generated, you should mark either complete databases or databases instances.

Restore Options

Bacula Enterprise Edition is made to give the user many options in the data recovery process, and restore the data in a variety of different ways. Some of these options are commonly used when restoring like:

  • the Where parameter: As for the usual Bacula Enterprise Edition features, the parameter allows the administrator to restore the data in a specific place.
  • the Replace parameter: It is used to dictate how Bacula should behave with existing data when restoring.

In addition to this, the SQL Server backup plugin itself brings more restore options that are more comprehensive, such as:

  • Instance: As MS SQL handles several instances, the Bacula Enterprise Edition MS SQL plugin allows you to choose which one to restore. Fine granularity is the key. This parameter is optional and if not set, the restore will use the MS SQL Backup and Restore value set at the backup time. By default, the instance name is “MSSQLSERVER”.
  • Database. This option specifies the name of the databases to restore, and it uses the MS SQL Backup and Restore value set at the backup time. This parameter is optional. By default, the plugin will use the Where option to determine the name of the new database. If both Where and Database are set to a valid database name, Database will be used.
  • User. This is the username used to connect to the MS SQL instance. This parameter is optional, and if not set, the restore will use the value set at the backup time.
  • Password. The password used to connect to the MS SQL instance. This parameter is optional and if not set, the restore will use the value set at the backup time.
  • Domain. This is for the domain used to connect to the MS SQL instance. This parameter is optional and if not set, the restore will use the value set at the backup time.
  • Recovery. This specifies if the database will use the RECOVERY or the NORECOVERY option during the restore. By default, the restored database will be recovered.
  • Stop_before_mark. For use the WITH STOPBEFOREMARK = <point> clause to specify that the log record that is immediately before the mark is the recovery point. The point can be a date time, an LSN number or a mark_name.
  • Stop_at_mark. For use the WITH STOPATMARK = <point> clause to specify that the marked transaction is the recovery point. STOPATMARK rolls forward to the mark and includes the marked transaction in the roll forward. The point can be a date time, an LSN number or a mark_name.
  • Stop_at=<datetime>. For use the WITH STOPAT = <datetime> clause to specify that the date time is the recovery point.
  • Restrict_user. For use the WITH RESTRICT_USER clause to restrict access to the restored database. The default is no.

On Bacula System’s BWeb Management Suite, the Plugin Options are available in the restore tab.

Restore options tab when using BWeb Management Suite

Point In Time SQL Server Restore

This topic is relevant only for SQL Server databases that use the full or bulk-logged recovery models. Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.

It is possible to do Point In Time Restore of a MS SQL database directly from the MS SQL Plugin. It is also possible to restore files locally and do the operation from the Microsoft SQL Server Management Console to have more options.

LSN Information

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken.

The LSN of a log record at which a given backup and restore event occurred is viewable using one or more of the following:

  • Bacula Backup job output
  • Log file names
  • msdb.backupset table
  • msdb.backupfile table

During a backup job with MS SQL module, the following information about LSN numbers will be displayed in the Job output:

 

win-fd JobId 3: LSN for "db29187": First: 42000146037, Last: 44000172001

The First LSN number corresponds to the last LSN of the last transaction logs backup. It can be the very first Full backup, or the last transactional backup (Incremental). The Last LSN number corresponds to the last transaction recorded in the log.

With a transaction log backup (Incremental), the file name associated with this database in the Incremental job will be:

 

/@mssql/MSSQLSERVER/db29187/log-42000162001.trn

The number in the name, here 42000162001 corresponds to the last LSN of the previous job (Full or Incremental).

sql server full database backupIn the example showed on Fig 2, if the administrator needs to restore the database at the state that corresponds to LSN 14, it can be done with the following actions:

  • Use restore menu option 5
  • Browse the database directory “/@mssql/db29187”
  • Select last Full backup file “data.bak” (LSN: 10)
  • Select incremental backup “log-10.trn”
  • Specify the stop_at_mark option to “lsn:14”
  • Run the restore job

or if the last full SQL backup is not available but the previous full backup is.

  • Use restore menu option 3, select the relevant jobids
  • Browse the database directory “/@mssql/db29187”
  • Select Full backup file “data.bak” (LSN: 2)
  • Select incremental backups “log-2.trn”, “log-3.trn”, “log-10.trn”
  • Specify the stop_at_mark option to “lsn:14”
  • Run the restore job

SQL Restore Scenarios Overview

DescriptionWhereDatabaseExample
Restore files to diskPathwhere=c:/tmp
Restore original databasewhere=/
Restore with a new nameNamewhere=newdb
Restore with a new nameNamedatabase=newdb
Restore with a new name and file relocationPathNamewhere=c:/tmp
database=newdb

Restore Scenarios

Restore With Same Name

To restore a database with the same name, the where parameter should be empty or “/” and the replace= flag should be set to always or the original database should be dropped first.

 

* restore where=/ replace=always
...
Using Catalog "MyCatalog"
Run Restore job
JobName: RestoreFiles
Bootstrap: /opt/bacula/working/127.0.0.1-dir.restore.1.bsr
Where: /
Replace: Always
FileSet: Full Set
Backup Client: win2008-fd
Restore Client: win2008-fd
Storage: File
When: 2015-12-14 09:53:36
Catalog: MyCatalog
Priority: 10
Plugin Options: *None*
OK to run? (yes/mod/no):


Restore SQL Database With a New Name

To restore a database with a new name, it might be required to relocate database files on disk. It depends if the original database is still present.

If the original database is no longer available, the where parameter or the “Plugin Options” database can contain the new database name, and the plugin will automatically handle the database creation with the new name.

If the original database is still required, the where parameter is used to relocate files on disk, and the new database name should be be set with the “Plugin Options” menu with the database option. The layout.dat must be selected in the restore tree.

 

* restore where=c:/tmp replace=always
...
Using Catalog "MyCatalog"
Run Restore job
JobName: RestoreFiles
Bootstrap: /opt/bacula/working/127.0.0.1-dir.restore.1.bsr
Where: c:/tmp
Replace: Always
FileSet: Full Set
Backup Client: win2008-fd
Restore Client: win2008-fd
Storage: File
When: 2015-12-14 09:53:36
Catalog: MyCatalog
Priority: 10
Plugin Options: *None*
OK to run? (yes/mod/no): mod <-----------------
Parameters to modify:
1: Level
2: Storage
3: Job
4: FileSet
5: Restore Client
6: When
7: Priority
8: Bootstrap
9: Where
10: File Relocation
11: Replace
12: JobId
13: Plugin Options
Select parameter to modify (1-13): 13 <-----------------
Automatically selected : mssql: database=db29187
Plugin Restore Options
instance: *None*
database: *None*
username: *None*
password: *None*
domain: *None*
recovery: *None* (yes)
stop_before_mark: *None*
stop_at_mark: *None*
stop_at: *None*
Use above plugin configuration? (yes/mod/no): mod <------------------
You have the following choices:
1: instance (Instance used to restore)
2: database (New database name)
3: username (Username used for restore)
4: password (Password used for restore)
5: domain (Domain name of user (default to local))
6: recovery (Start Recovery)
7: stop_before_mark (Stop the recovery before a mark (STOPBEFOREMARK).
8: stop_at_mark (Stop the recovery at a mark (STOPATMARK).
9: stop_at (Stop at (STOPAT). {datetime})
Select parameter to modify (1-9): 2 <------------------
Please enter a value for database: newdb <------------------
Use above plugin configuration? (yes/mod/no): yes <------------------
Using Catalog "MyCatalog"
Run Restore job
JobName: RestoreFiles
Bootstrap: /opt/bacula/working/127.0.0.1-dir.restore.1.bsr
Where: c:/tmp
Replace: Always
FileSet: Full Set
Backup Client: win2008-fd
Restore Client: win2008-fd
Storage: File
When: 2015-12-14 09:53:36
Catalog: MyCatalog
Priority: 10
Plugin Options: User Specified
OK to run? (yes/mod/no): yes <-----------------

Restore SQL to Local Disk

When specifying where=c:/path/, files will be restored to the local filesystem and the MS SQL administrator can use a TSQL or the Microsoft SQL Server Mangement Console to restore the database. SQL commands needed to restore the database are printed in the Job output as showed in the next example.

 

* restore where=c:/tmp
First you select one or more JobIds that contain files
to be restored. You will be presented several methods
of specifying the JobIds. Then you will be allowed to
select which files from those JobIds are to be restored.
To select the JobIds, you have the following choices:
1: List last 20 Jobs run
2: List Jobs where a given File is saved
3: Enter list of comma separated JobIds to select
4: Enter SQL list command
5: Select the most recent backup for a client
6: Select backup for a client before a specified time
7: Enter a list of files to restore
8: Enter a list of files to restore before a specified time
9: Find the JobIds of the most recent backup for a client
10: Find the JobIds for a backup for a client before a specified time
11: Enter a list of directories to restore for found JobIds
12: Select full restore to a specified Job date
13: Cancel
Select item: (1-13): 5
Automatically selected Client: win2008-fd
+-------+-------+----------+----------+---------------------+---------------+
| jobid | level | jobfiles | jobbytes | starttime | volumename |
+-------+-------+----------+----------+---------------------+---------------+
| 1 | F | 3 | 65,771 | 2015-12-14 09:52:31 | TestVolume001 |
| 2 | I | 2 | 65,771 | 2015-12-14 09:52:42 | TestVolume001 |
| 3 | I | 2 | 65,771 | 2015-12-14 09:52:52 | TestVolume001 |
+-------+-------+----------+----------+---------------------+---------------+
You have selected the following JobIds: 1,2,3
Building directory tree for JobId(s) 1,2,3 ...
6 files inserted into the tree.
You are now entering file selection mode where you add (mark) and
remove (unmark) files to be restored. No files are initially added, unless
you used the "all" keyword on the command line.
Enter "done" to leave this mode.
cwd is: /
$ cd @mssql
cwd is: /@mssql/
$ cd MSSQLSERVER
cwd is: /@mssql/MSSQLSERVER/
$ m db1684
6 files marked.
$ done
Bootstrap records written to /opt/bacula/working/127.0.0.1-dir.restore.1.bsr
The Job will require the following (*=>InChanger):
Volume(s) Storage(s) SD Device(s)
===========================================================================
TestVolume001 File FileStorage
Volumes marked with "*" are in the Autochanger.
2 files selected to be restored.
Using Catalog "MyCatalog"
Run Restore job
JobName: RestoreFiles
Bootstrap: /opt/bacula/working/127.0.0.1-dir.restore.1.bsr
Where: /tmp
Replace: Always
FileSet: Full Set
Backup Client: win2008-fd
Restore Client: win2008-fd
Storage: File
When: 2015-12-14 09:53:36
Catalog: MyCatalog
Priority: 10
Plugin Options: *None*
OK to run? (yes/mod/no): yes
Job queued. JobId=6
wait
You have messages.
* messages
$ done
17:18 dir JobId 6: Start Restore Job RestoreFiles.2015-12-14_17.18.18_14
17:18 dir JobId 6: Using Device "FileStorage" to read.
17:18 sd JobId 6: Ready to read from volume "TestVolume001" on file device "FileStorage" (/tmp/regress/tmp).
17:18 sd JobId 6: Forward spacing Volume "TestVolume001" to file:block 0:224.
17:18 fd JobId 6: RESTORE DATABASE [db1684] FROM DISK='c:/tmp/@mssql/MSSQLSERVER/db1684/data.bak'
WITH BLOCKSIZE=65536, BUFFERCOUNT=10, MAXTRANSFERSIZE=65536, NORECOVERY , REPLACE
17:18 fd JobId 6: RESTORE DATABASE [db1684]
FROM DISK='c:/tmp/@mssql/MSSQLSERVER/db1684/log-34000000014400001.bak'
WITH BLOCKSIZE=65536, BUFFERCOUNT=10, MAXTRANSFERSIZE=65536, NORECOVERY
17:18 fd JobId 6: RESTORE DATABASE [db1684]
FROM DISK='c:/tmp/@mssql/MSSQLSERVER/db1684/log-34000000018400001.bak'
WITH BLOCKSIZE=65536, BUFFERCOUNT=10, MAXTRANSFERSIZE=65536, NORECOVERY
17:18 fd JobId 6: RESTORE DATABASE [db1684]
FROM DISK='c:/tmp/@mssql/MSSQLSERVER/db1684/log-34000000029100001.bak'
WITH BLOCKSIZE=65536, BUFFERCOUNT=10, MAXTRANSFERSIZE=65536, NORECOVERY
17:18 sd JobId 6: End of Volume at file 0 on device "FileStorage" (/tmp/regress/tmp), Volume "TestVolume001"
17:18 fd JobId 6: RESTORE DATABASE [db1684]
FROM DISK='c:/tmp/@mssql/MSSQLSERVER/db1684/log-36000000017200001.bak'
WITH BLOCKSIZE=65536, BUFFERCOUNT=10, MAXTRANSFERSIZE=65536, NORECOVERY
17:18 sd JobId 6: Elapsed time=00:00:01, Transfer rate=9.372 M Bytes/second
17:18 fd JobId 6: RESTORE DATABASE [db1684]
17:18 dir JobId 6: Bacula dir 8.4.8 (22Feb16):
Build OS: x86_64-unknown-linux-gnu archlinux
JobId: 6
Job: RestoreFiles.2015-12-11_17.18.18_14
Restore Client: win2008-fd
Start time: 14-Dec-2015 17:18:20
End time: 14-Dec-2015 17:18:22
Files Expected: 6
Files Restored: 6
Bytes Restored: 9,371,785
Rate: 4685.9 KB/s
FD Errors: 0
FD termination status: OK
SD termination status: OK
Termination: Restore OK

Restore the "master" Database

Instructions on how to restore the “master” database are detailed in this article: https://technet.microsoft.com/en-us/library/aa213839%28v=sql.80%29.aspx

Database in restoring State

At the end of a restore, if the plugin option recovery was set to no, the restored database will be in the “restoring” state. To end the restore process, the recovery process must be run. It can be done with the following SQL command:

 

RESTORE [yourdatabase] WITH RECOVERY;


Further help on SQL backup: