Bacula - Backing up SQLServer
Published: 2007-02-23
Category: Tech
Tags:
Language: [English]
page views: 384

Backing-up SQL-Server is not a trivial thing. Since SQL-Server's own backup system is really too flaky for backup purposes. It uses the SQL Server agent  which has the feature that of one job fails to terminate, none of the following jobs gets executed. Really nice when the next jobs create the backups *sigh*. When will Microsoft get it, this is no longer the '80s!.

The resulting backups are, in true microsoft fashion, just binary blobs of the database, nothing human readable in that. In fact, you can't even restore a backup in a machine that has a different physical disk layout (!!!). Remember kids, once you give data to Microsoft it is designed to give you maximum problems when you want to take it back out.

Anyway to make Bacula backup this overgrown toy database system. the following is useful.

Microsoft SQL Server In theory, if you are running SQL Server 2000 or higher on Windows 2003 Server with a VSS enabled Bacula client, you should be able to safely back up the live database files. This is not known to be tested and verified at this time, however, so the recommended solution is to use SQL Servers built in backup facility to dump the database contents to a file which bacula can then safely read. In order to do so, you will need to arrange a %u2018Client Run Before Job%u2019 script that will execute a SQL command like BACKUP DATABASE yourdb TO DISK='C:\temp\yourdb.bak' WITH INIT for each database you are backing up. You would then later restore the file with a SQL command like RESTORE DATABASE yourdb FROM DISK='C:\temp\yourdb.bak' Make sure you consult the documentation for your version of SQL Server to ensure you are using the right commands, and to back up any other database information you require such as transaction logs. For more information on backing up SQL Server 2000, go to http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx