Loading...SQL Server is one of the most popular database management systems - if not the most popular one. As with any popular system, there are different solutions to help with SQL database management and/or SQL database backup and restore processes. Any database that might contain important data should be correctly backed up, and this applies of course to your SQL database, too. Backing up is strongly recommended in order to mitigate the majority - or even all - of the consequences that come with a corruption or loss of your database.
Some backup solutions tend to offer a lot of backup options and targets in one package. Other backup solutions are more about focusing on only one or two backup targets at once (for example, only working with SQL Server databases) with much more versatility and customizability than most of the “all-in-one” competitors. Bacula is considered to be one of the “several options in one solution” type of software, offering both free SQL backup software and a number of other options at the same time. However, despite the fact that it is free software, it offers a lot of different backup destinations and targets, with especially high levels of versatility and customizability.
Usually Bacula Enterprise provides modules or plugins to work with different backup targets (different storage types, different server types, etc). However, that’s not necessary in some cases. For example, while there’s no specific SQL-related plugin for Bacula Community version, it’s still possible to perform backups of your SQL database through converting databases in .bak file types and backing up those files via standard backup procedures.
The conversion of an active database into a specific file type is sometimes called “creating a dump”. The preparation step of the process is minimal in Bacula’s free SQL backup software, the only thing you will need to perform is to acquire the “sysadmin” permission level in the “SQL Server” (even if you have “SYSTEM” level privileges in Bacula in general), and you can only perform one “dump” of a database at a time.
There are several ways of performing this process. The easier one includes performing everything via a .bat file with specific command sequence. Please note that you have to create a specific directory on your SQL server to place this .bat file. In this example we create a folder C:\DatabaseBackups and one more directory inside that folder, called \scripts. Our .bat file name is SQL.bat. The contents of this file are as follows:
del “C:\DatabaseBackups\DB1.bak”
del “C:\DatabaseBackups\DB2.bak”
del “C:\DatabaseBackups\DB3.bak”sqlcmd -S .\DATABASE1 -Q “BACKUP DATABASE DB1 TO DISK = ‘C:\DatabaseBackups\DB1.bak’ WITH INIT, NAME = N’Automatic back up of database’, STATS = 1”
sqlcmd -S .\DATABASE1 -Q “BACKUP DATABASE DB2 TO DISK = ‘C:\DatabaseBackups\DB2.bak’ WITH INIT, NAME = N’Automatic back up of database’, STATS = 1”
sqlcmd -S .\DATABASE1 -Q “BACKUP DATABASE DB3 TO DISK = ‘C:\DatabaseBackups\DB3.bak’ WITH INIT, NAME = N’Automatic back up of database’, STATS = 1”
exit
This script performs a deletion of the older backups that were created the last time this job was performed, and then creates newer backups. The entire process may take a while depending on your general database size, but Bacula would be instructed to wait until the process is done before attempting to back up those files anyway.
The next step of this process is to set up this .bat script to run before the backup job is performed. This can be done using the “Client Run Before Job” option, specifying the script location at the same time. Here’s an example of how the job config should look after adding this option:
Job {
Name = My-Databases
Type = Backup
Level = Incremental
Client = database-fd
FileSet = database-FS
Schedule = WeeklyCycle
Storage = BackupSD
Pool = BackupPool
Messages = Standard
Client Run Before Job = C:\DatabaseBackups\script\sql.bat
Write Bootstrap = “/BackupNAS/BaculaBootstraps/%c_%n.bsr”
}
Once the entire backup process is complete, you should start getting status messages, specifying the progress of database backup being performed (as well as other status messages), an example of this is also specified below:
26-Jan 10:00 database-fd JobId 11322: ClientRunBeforeJob: 96 percent processed.
26-Jan 10:00 database-fd JobId 11322: ClientRunBeforeJob: 97 percent processed.
26-Jan 10:00 database-fd JobId 11322: ClientRunBeforeJob: 98 percent processed.
26-Jan 10:00 database-fd JobId 11322: ClientRunBeforeJob: 99 percent processed.
26-Jan 10:00 database-fd JobId 11322: ClientRunBeforeJob: Processed 111384 pages for database
There’s also another, more sophisticated way of performing an SQL server backup. Make sure you have VSS enabled, first, so that the process could be performed to begin with.
This way of setting up a backup is to create a job by changing an existing backup job’s config file.
# vi /etc/bacula/client.d/example.net.conf
....
FileSet {
....
Include {
....
# Backup database target directory
File = "C:/bacula-mssql"
}
}
....
Job {
Name = "example.net"
Type = Backup
....
# Starting the upload of the binary SQL backup copy:
RunScript {
RunsWhen = Before
FailJobOnError = No
# Cleaning up and/or creating a place to save the “dump”
Command = "if exist c:\\bacula-mssql rmdir /s /q c:\\bacula-mssql"
Command = "mkdir c:\\bacula-mssql"
# Starting up the upload for a database backup
# (you’ll have to input your database’s name instead of “dbname” in the next line)
Command = "SET dbname=example_DB & sqlcmd -Q \"SET LANGUAGE us_english; BACKUP DATABASE $(dbname) TO DISK='C:\\bacula-mssql\\$(dbname).bak'\""
}
#
RunScript {
RunsWhen = After
RunsOnFailure = yes
# Offloading resources after everything is complete
Command = "if exist c:\\bacula-mssql rmdir /s /q c:\\bacula-mssql"
}
}
And here’s how you can check if everything was set up correctly:
# bacula-dir -c /etc/bacula/bacula-dir.conf -t
# /etc/init.d/bacula-dir reload
The vast versatility and variety of functions makes Bacula one of - if not the - best free SQL backup software solutions available.
If you’re also interested in Hyper V Backup, check out Bacula’s solution.
