Scheduling Backups on SQLServer Express

Management Studio Express does not provide a user interface to schedule backup jobs, but you can easily find a couple of solutions on the web, such as here, here, and here.

The backup solution I came up with was one .sql file each for full and incremental backup, and two .cmd files which execute the respective .sql files via sqlcmd.

The full backup SQL file looks like this:

PRINT 'backup.sql ' + CONVERT(VARCHAR, GETDATE(), 120)

DECLARE @backupSetId AS INT
DECLARE @Filename NVARCHAR(256)
DECLARE @Database NVARCHAR(256)
DECLARE @Backup NVARCHAR(256)

SET @Database = N'<name of database>'
SET @Filename = N'E:\Backups\<name of database>.' +
    REPLACE(CONVERT(NVARCHAR, GETDATE(), 102), '.', '-') +
    N'.bak'
SET @Backup = @Database + N' Full Database Backup'

BACKUP DATABASE @Database
TO DISK = @Filename
WITH NOFORMAT, NOINIT,
NAME = @Backup,
SKIP, NOREWIND, NOUNLOAD, STATS = 10

SELECT    @backupSetId = position
FROM    msdb..backupset
WHERE    database_name = @Database
AND        backup_set_id =
    (SELECT MAX(backup_set_id) FROM msdb..backupset
        WHERE database_name=@Database )

IF @backupSetId IS NULL
    PRINT N'Verify failed. Backup information for database ''' +
        @Database + N''' not found.'
ELSE
    RESTORE VERIFYONLY FROM  DISK = @Filename
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

This piece of code is repeated for every database you want to backup.

The incremental backup file has the WITH DIFFERENTIAL option in the BACKUP command, and excludes verification.

The batch file which executes the backup.sql file simply calls sqlcmd:

sqlcmd -S <your db server name>
    -i <path to>\backup.sql
    -o <path to>\backup.log

Next, use the Scheduled Tasks control panel application to schedule the full backup once a week, and the incremental backups daily: Use a weekly schedule and select the days of week to define when the backups should run.

This entry was posted on Monday, June 16th, 2008 at 22:41 and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Scheduling Backups on SQLServer Express”

Introducing automssqlbackup « devioblog Says:
February 10, 2009 at 12:17 | Reply
[...] sketched one possible solution some time ago, calling T-SQL commands from a batch file which is executed as a Scheduled [...]

devioblog