Backing Up Express
One of the most common complaints I hear about SQL Express is that because it doesn’t include SQL Agent, there is no way to schedule backups. I won’t go into the reasons for why Agent is not in Express but I thought it would be good to offer an alternative. The script that follows uses the Service Broker feature to backup a database once a day. I didn’t include log backups because by default SQL Express databases use the SIMPLE recovery model so log backups aren’t necessary. If you want to do log backups because you use one of the other recovery models, a few minor changes would transform this script into a log backup script. If you want to do some more elaborate scheduling, you might get some ideas here: http://www.microsoft.com/technet/technetmag/issues/2005/05/ServiceBroker/default.aspx
The following script uses the CONVERSATION TIMER feature of SQL Server 2005 Service Broker to schedule backups. Basically, a CONVERSATION TIMER puts a message on a Service Broker queue at some specified time in the future. When the message arrives on the queue, Service Broker will start the Activation procedure (HandleBackUp in this case) to receive the message. In this sample, HandleBackUp just resets the CONVERSATION TIMER for 24 hours from now and backs up the database. To make it easier for you to see what’s happening, a row is inserted into a table called “BackStatus” every time a backup is completed.
The sample backs up a database named “AccountsPayable” so you will have to change that to the name of your database. The key thing to remember about the way this script works is that it runs in the database that is being backed up. That means that if you have a number of databases to back up, you just run the script in each database. In fact, if you have a script that creates new databases, you could add this to the database as part of the creation process. The cool thing about this is that you can move this database to another SQL Server instance and the backup will continue to run every night.
Here is the basic script (watch out for line wraps - especially in comments):
-- Create a table to log the backups we have done.
-- This probably isn't necessary but it's makes it
-- easier to see what's happening in this sample.
CREATE TABLE BackStatus
( BackUpId int identity primary key,
BackupStatus nvarchar(1000) )
-- Set up a Service Broker queue for the BackUp sample
CREATE QUEUE [BackUpQueue]
CREATE SERVICE [BackUpService] ON QUEUE [BackUpQueue]([DEFAULT]) ;
CREATE SERVICE [BackUpResponseService] ON QUEUE [BackUpQueue];
-- Create the stored procedure to handle the conversation timer messages
CREATE PROCEDURE [dbo].[HandleBackUp] AS
DECLARE @BackupFileName nvarchar(200)
DECLARE @BackupInterval int
DECLARE @BackupStatus nvarchar(1000)
DECLARE @DBName sysname
DECLARE @conversationHandle uniqueidentifier
DECLARE @message_type_name sysname
DECLARE @dialog uniqueidentifier
-- Set up the backup parameters.
-- These will need to be changed for your situation
-- Set the name of the database to backup
SET @DBName = 'AccountsPayable'
-- Create the filename. Note that this only includes
-- the date so if you are going to backup more than once
-- a day, you will need to use a convert format that
-- includes the time.
SET @BackupFileName = @DBName + convert(nvarchar(20),
getdate(), 112) + N'.bak'
-- set the backup interval to 24 hours (86400 seconds)
SET @BackupInterval = 86400
-- Receive the next message on the queue
-- BackUp messages only happen once a day so there's no
-- need to receive in a loop.
@dialog = conversation_handle
), TIMEOUT 500
IF (@message_type_name =
-- Start the next cycle
BEGIN CONVERSATION TIMER ( @dialog )
TIMEOUT = @BackupInterval;
-- Must commit here because backup doesn't
-- work in a transaction
BACKUP DATABASE @DBName TO DISK = @BackupFileName
WITH CHECKSUM, INIT;
SET @BackupStatus = ERROR_MESSAGE()
-- Insert a record into the backup log
INSERT INTO BackStatus ( DatabaseName, LastBackupTime,
LastBackupFile, BackupType, BackupStatus) VALUES
(@DBName, GETDATE(), @BackupFileName,
-- For errors and end dialog, just end the dialog.
-- We don't have to cleanup.
END CONVERSATION @dialog
ELSE IF (@message_type_name =
ALTER QUEUE [BackUpQueue] WITH ACTIVATION (
STATUS = ON, PROCEDURE_NAME = HandleBackUp ,
MAX_QUEUE_READERS = 1,EXECUTE AS SELF)
Do a search and replace of AccoutsPayable to the name of the database you want to backup and then run this script. This script will run once every 24 hours but to start it going you have to create a dialog and start a conversation timer. The following script should only be run once to get that process started:
-- This part is run just once to start the backup
-- process running. Once it is started, it will
-- run the backup once a day forever.
DECLARE @conversationHandle uniqueidentifier
DECLARE @Timeout int
-- This sets the backup to happen at midnight every day.
-- If you need a diferent time compute the number of seconds
-- between now and the time you want the first backup
-- to run and enter it as @Timeout
SET @Timeout = DATEDIFF(SECOND, GETDATE(),
DATEADD (DAY, 1, convert(nvarchar(20),GETDATE(),112)));
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [BackUpResponseService]
TO SERVICE 'BackUpService';
BEGIN CONVERSATION TIMER ( @ConversationHandle )
TIMEOUT = @Timeout;
Once this is started, it will repeat every 24 hours until you stop it by calling END CONVERSATION on the dialog in use. You can find the conversation to end with this query:
SELECT conversation_handle, dialog_timer FROM sys.conversation_endpoints
Note that the dialog_timer time is GMT so it may not look like it expires at midnight.
Since I didn’t specify a full path for the filename, the files will be placed in the default backup directory. If Express is the first instance of SQL Server on your machine, that will be:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
You can obviously change the script to put them anywhere you want but Express runs as the “Network Service” user which doesn’t have many permissions so it’s safe to use the default location so Express can write the files.