Here is my way. I’ve actually had this up and running for some time in another environment using SQL Server Standard 2008. I’m not in need of configuring a new backup for a 2012 SQL Express instance. There are a few parts, obviously. This article assumes you can configure a Bareos Windows client, if not there are plenty of other tutorials to help with that.
Part 1 is to create a sql command that will backup your databases to a file in the location of your choosing. I choose to keep all of my scripts as well as backup files in the same location so that if I ever have to restore I can figure out what it is exactly I did to get the backup working in the first place.
- Launch SQL Server Management Studio (go get a cup of coffee while you wait for it to load)
- Connect using whatever credentials give you some pretty hefty rights to the database or databases you want backed up.
- Drill into the Database server, Databases menu items, then right click on the database you want to work with, select Tasks and Back Up…
- The only thing you should have to change in the resulting dialogue is where you want it saved. The default will work fine, but as mentioned I recommend keeping backup scripts and backup files in 1 easy to find work area. For me it’s going to be nice and easy “c:\dbbackups”. There may be performance or capacity implications you’ll have to take to mind in your environment. Also if you do like me and create a folder at the root it’s a good idea to pair back the root permissions on that folder.
- Don’t hit OK, that will back up your database, not necessarily what you want now. Tap the down arrow near the Script button at the top of the dialogue and choose “Script action to file”. For me I’m putting it in the folder mentioned before.
Step 2 is to create a script or batch file or something that Bareos can call to have the backup run. The file you execute should not complete until the backup file is created so that Bareos doesn’t try to backup the file until it exists. I believe Bareos even halts and “fails” the backup if the return status from the script is not 0, I’ll probably verify that later. Mine is a simple file that deletes yesterday’s backup then creates today’s backup:
erase "C:\dbbackup\*.bak" sqlcmd -E -i "C:\dbbackup\backup.sql"
I could add more logic here, but it seems I don’t need to. This has been reliable for me in the past.
Next Step is to configure the backup FileSet, Job and Schedule. Here are what mine look like:
FileSet {
Name = "c_dbbackups"
Include {
Options {
Signature = MD5
Drive Type = fixed
IgnoreCase = yes
}
File = c:/dbbackups
}
}
Job {
Name = "hostname_db"
Type = Backup
FileSet = "c_dbbackups"
Schedule = "NightlyFull_2000"
Storage = File
Messages = Standard
Priority = 10
Pool = Database
Client = "hostname-fd"
ClientRunBeforeJob = "c:/dbbackups/backup_db.cmd"
}
Schedule{
Name = "NightlyFull_2000"
Run = Level=Full sun-sat at 20:00
}
The obvious key component is the ClientRunBeforeJob directive in the Job definition. This makes sure to run the MS SQL backup prior to running the Bareos file backup.
I should mention the reason I’m doing Full nightly… obviously this method could be renamed cheapass backup. As such there is no interaction between the actual SQL backup and the file backup Bareos is performing. You could do a differential backup (and I have in other installations where bytes are more scarce and databases are bigger) but the actual differential part of it is done way back in step one when you’re creating the MSSQL backup script. If you do this I recommend backing up to a separate file in that same all encompassing directory, that way all the crap you need is there, the scripts, the full, and the diff. If you’re backing up a 500GB DB and you only have a couple T to store to… you’ll have to do something like this.
And then we test… Did you expect this error, I did?
ClientBeforeJob: The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "DATABASENAME" under the current security context.
So last step is we have to run that .CMD in a security context that has rights to backup the database. The easy solution is to go back into your SQL Server Management Studio, expand the DB server, Security, and Logins then right-click on NT AUTHORITY\SYSTEM and open the properties dialogue. In there highlight Server Roles in the left pane then check sysadmin in the right pane.
After this you can login to BAT or bconsole or however you choose and test your job again.
Of course even if it appears to work, you should test your restores, which is a whole different ball of wax. If you’re lucky like me you have a test server that you can do your restore to since testing restores on a production MSSQL system is an absolute bear. Remember, if you haven’t tested restores, you don’t have backups!
