Partager via

Backing Up Express

  • Article

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: https://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):

Use AccountsPayable

go

-- 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,

      DatabaseName sysname,

      LastBackupTime datetime,

      LastBackupFile nvarchar(200),

      BackupType nvarchar(20),

      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];

GO

-- 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

      BEGIN TRANSACTION

      -- Receive the next message on the queue

      -- BackUp messages only happen once a day so there's no

      -- need to receive in a loop.

      WAITFOR (

            RECEIVE top(1)

                  @message_type_name=message_type_name,

                  @dialog = conversation_handle

                  FROM [BackUpQueue]

      ), TIMEOUT 500

      IF (@message_type_name =

            'https://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer')

      BEGIN

            -- Start the next cycle

            BEGIN CONVERSATION TIMER ( @dialog )

                  TIMEOUT = @BackupInterval;

            -- Must commit here because backup doesn't

            -- work in a transaction

            COMMIT TRANSACTION

            BEGIN TRY

                  BACKUP DATABASE @DBName TO DISK = @BackupFileName

                        WITH CHECKSUM, INIT;

            END TRY

            BEGIN CATCH

                  SET @BackupStatus = ERROR_MESSAGE()

            END CATCH;

            -- Insert a record into the backup log

            INSERT INTO BackStatus ( DatabaseName, LastBackupTime,

                        LastBackupFile, BackupType, BackupStatus) VALUES

                        (@DBName, GETDATE(), @BackupFileName,

                        'Database', @BackupStatus);

      END

      ELSE

      -- For errors and end dialog, just end the dialog.

      -- We don't have to cleanup.

      IF (@message_type_name =

            'https://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

      BEGIN

            END CONVERSATION @dialog

            COMMIT TRANSACTION

      END

      ELSE IF (@message_type_name =

            'https://schemas.microsoft.com/SQL/ServiceBroker/Error')

      BEGIN

            END CONVERSATION @dialog

            COMMIT TRANSACTION

      END

go

ALTER QUEUE [BackUpQueue] WITH ACTIVATION (

            STATUS = ON, PROCEDURE_NAME = HandleBackUp ,

            MAX_QUEUE_READERS = 1,EXECUTE AS SELF)

Go

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.

Comments