"BTS2004 provides supported scripts for backing up BTS databases transactionally. It is important to make sure that all database backups are done transactionally so [MS] provides a sql agent job [called Backup BizTalk Server] in the management database server which uses linked server (if the other dbs are remote) to go to each database in the system and transactionally mark the log. It then takes a backup of all logs and truncates the logs [to control database growth]. All you have to do is enable the job to run and set a couple of parameters. All of this is documented in the help file. Taking a backup of each database by itself is not usefull since you cannot restore them individually always or else you will break transactional integrity." -- Lee Graber
The on-line documentation covers this topic pretty well and if I'd read it more thoroughly I'd have found this SQL job. If you look at the database write diagram in the on-line help, it becomes much clearer why using this SQL job is the only way to back-up your BTS databases. The parameters that Lee talks about are shown below and in the on-line help file.
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '' /* location of backup files */
'd' = daily full backups. You can change this period to hourly (h/H), weekly (w/W), monthly (m/M), or yearly (y/Y). The first time the job is run during a new period, a full backup is performed. 'BTS' is the default for Name. '' for the destination path. You must change this to a valid destination such as 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP'. The destination path may be local or a UNC path to another server.
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, ' ' /* location of backup files */
'BTS' is the default for Log Mark Name. Change as needed. Log Mark Name is part of the naming convention for backup files: __Log_< Log Mark Name >_ and '' for the destination path. You must change this to a valid destination such as C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP'. The destination path may be local or a UNC path to another server.
MS also makes a few key points to remember on this topic.
1. The SQL job does not delete old backup files. You must manage those files to conserve disk space by backing them up and deleting them after new full backups have been created. (I wonder how to automate this?)
2. Do not store the backup files on the same storage system as the database, because in the event of a failure, both the backups and the databases would be lost. (We currently perform disk-to-disk and then disk-to-tape backups for all vital SQL databases.)
3. It is recommended that you practice restoring your databases at least once a month. Do not wait until your hard disk crashes to see if you can restore your system and databases. (First thing Monday morning on my test server!)