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.

About these ads

2 Responses to Scheduling Backups on SQLServer Express

  1. [...] sketched one possible solution some time ago, calling T-SQL commands from a batch file which is executed as a Scheduled [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: