Microsoft SQL Server Database Backup with Bacula
Here are some tips on how to back up a Microsoft SQL database .
You can configure periodic backup MSSQL through its Backup Plan functionality to the local disk , and then make a copy of these files with Bacula as usual.
Alternatively we can set up a ClientClientBeforeJob windows script (this considering that this bank is installed on a machine with the Bacula client) to generate the database dump that must be saved by Bacula also as regular files.
Setting ClientRunClientBeforeJob script:
The script should be a “.bat ” Windows file (e.g.: C:\mssql_bkp.bat) , containing something like the following commands :
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe" -U db_user -P db_passwd -E -Q "BACKUP DATABASE mydatabase TO DISK='C:\backup_mssql\mydatabase.bak' WITH FORMAT"
*The osql.exe path may vary.
No bacula-dir.conf, no recurso “Job” específico para backup deste servidor, você deve configurar o Bacula para chamar o script criado:
Name = Mssql_server_bkp
ClientRunBeforeJob = C:/mssql_bkp.bat
Do not forget that in the corresponding fileset to that job , the file ” dump” created should be included!
You may also create a “ClientRunAfterJob” script to delete the generated dump files, since it is already copied to your storage with your other backups.
To restore the database , you must first restore the dump file by Bacula .
So then you can use the command line or Microsoft SQL graphical interface to restore it .