Backing up the K2 blackpearl databases is a very important task but with 14 databases that are now part of the K2 blackpearl platform, this task can be difficult if done manually.

In this post I will provide you with a script that can be used to easily backup all of the databases.

To make things easier you first need to create a backup device to save the backups to. You can do this from the SQL Server Management Console (under Server Objects > Backup Devices) or by executing the query below. (Change the values where applicable)

USE master
EXEC sp_addumpdevice 'disk', 'K2_Backup_Device',
'C:\K2Backups\AllK2Backups.bak'
GO

After you have the backup device set, backing up the databases are simple. You simply supply the backup a backup device to back up to. To make the management of the backup file easy, all databases can be backed up to the K2_Backup_Device device and there will be only one .bak file which you need to manage. Here is a script to backup all of the databases to the newly created device.

BACKUP DATABASE Categories
TO K2_Backup_Device
WITH FORMAT,
NAME = 'Categories Full Backup';
BACKUP DATABASE Dependencies
TO K2_Backup_Device
WITH NAME = 'Dependencies Full Backup';
BACKUP DATABASE EnvironmentSettings
TO K2_Backup_Device
WITH NAME = 'EnvironmentSettings Full Backup';
BACKUP DATABASE EventBus
TO K2_Backup_Device
WITH NAME = 'EventBus Full Backup';
BACKUP DATABASE EventBusScheduler
TO K2_Backup_Device
WITH NAME = 'EventBusScheduler Full Backup';
BACKUP DATABASE HostServer
TO K2_Backup_Device
WITH NAME = 'HostServer Full Backup';
BACKUP DATABASE K2Server
TO K2_Backup_Device
WITH NAME = 'K2Server Full Backup';
BACKUP DATABASE K2ServerLog
TO K2_Backup_Device
WITH NAME = 'K2ServerLog Full Backup';
BACKUP DATABASE K2SQLUM
TO K2_Backup_Device
WITH NAME = 'K2SQLUM Full Backup';
BACKUP DATABASE SmartBox
TO K2_Backup_Device
WITH NAME = 'SmartBox Full Backup';
BACKUP DATABASE SmartBroker
TO K2_Backup_Device
WITH NAME = 'SmartBroker Full Backup';
BACKUP DATABASE SmartFunctions
TO K2_Backup_Device
WITH NAME = 'SmartFunctions Full Backup';
BACKUP DATABASE WebWorkflow
TO K2_Backup_Device
WITH NAME = 'WebWorkflow Full Backup';
BACKUP DATABASE Workspace
TO K2_Backup_Device
WITH NAME = 'Workspace Full Backup';
GO

The script above will overwrite the backup file each time it is executed. This can be changed by removing the ‘WITH FORMAT’ clause of the first BACKUP statement.

You can take this script and create a SQL job from it to automate the backup procedure.

For more information on modifying any of the above scripts, see the “sp_addumpdevice (Transact-SQL)” or “BACKUP (Transact-SQL)” sections in SQL Server 2005 Books Online or access it online:

sp_addumpdevice (Transact-SQL)

BACKUP (Transact-SQL)