You can watch a short video about backing up instances of a SQL Server databases.
You should have already configured Yosemite Server Backup SQL Agent. See Configuring Backup for Microsoft SQL Server for more information. Two additional concerns are present when you back up SQL Server database instances: setting the Backup mode of a job to Full, Incremental or Differential; and configuring Yosemite Server Backup to work with SQL Server’s default backup routine.
Microsoft SQL Server Databases and the Backup Mode
The Backup mode on the Options tab of a job that backs up SQL Server database instances is especially critical.
When the Full setting is selected, all files selected for backup are backed up, including SQL Server database instances and databases. However, when either the Incremental or Differential option is selected, Yosemite Server Backup backs up only the transaction logs for each database.
When the Backup mode is set to Copy, Yosemite Server Backup a creates a full backup of the database but does not truncate the logs.
Master, Model, MSDB and Pubs databases support only full backups. The Backup mode option is automatically set to Full when backing up these databases.
If you set a job to run in either Incremental or Differential mode and the job can only run as a full backup (as a result of the provision above), the job will fail to run on each of its initial passes, but will run in Full backup mode on its final pass.
Using Yosemite Server Backup with the SQL Server Backup Routine
Microsoft SQL Server has default utilities and commands for backing up data. When you use Yosemite Server Backup to back up SQL Server databases, you can still use these default SQL Server utilities and commands. For example, you can use the DUMP command to dump transaction logs to the dump device (preferably, a separate disk drive). You can set this up to occur at regular intervals, such as every 15 minutes or every hour. Next, you can create a backup job that backs up these transaction logs onto archival media every day.
In general, when you implement Yosemite Server Backup to back up your SQL Server databases, continue to use SQL Server’s internal commands to duplicate and back up transaction logs. Set up a separate Yosemite Server Backup backup job to write these duplicated transaction logs to archival media.