Web & Software Techniques and Tips: How-to: Quick & Dirty SQL Express scheduled backup

SQL Express is pretty awesome as a light weight database server, and when Microsoft released it using the same database engine as the full version they did the world a huge favour. I have seen SQL Express in use on a lot of Virtual Private Servers & development boxes, but in most cases users don’t backup their databases regularly because of the missing scheduled jobs functionality that doesn’t ship in the Express version of Microsoft SQL Server. Like most limitations there is an easy way around this.

Open SQL Management Studio and copy the following SQL script i created earlier into a new query window.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================= 
-- Author: Doug Rathbone
-- Description: Backup Database Stored Proc
-- Param1: dbName 
-- Param2: backupType F=full, D=differential, L=log
-- ============================================= 
create PROCEDURE [dbo].[sp_BackupDb]  
       @dbName sysname, @backupTypeToRun CHAR(1) 
AS 
BEGIN 
       SET NOCOUNT ON; 

       DECLARE @sqlCommand NVARCHAR(1000) 
       DECLARE @dateTime NVARCHAR(20) 

       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','-') +'-' +
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
       
       DECLARE @databaseFileName NVARCHAR(200)
       SET @databaseFileName = replace(@dbName,']','')
       SET @databaseFileName = replace(@databaseFileName,'[','')

       IF @backupTypeToRun = 'F' 
               SET @sqlCommand = 'BACKUP DATABASE ' + @dbName + 
               ' TO DISK = ''C:\DbBackups\' + @databaseFileName + '_Full_' + @dateTime + '.BAK''' 
        
       IF @backupTypeToRun = 'D' 
               SET @sqlCommand = 'BACKUP DATABASE ' + @dbName + 
               ' TO DISK = ''C:\DbBackups\' + @databaseFileName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL' 
        
       IF @backupTypeToRun = 'L' 
               SET @sqlCommand = 'BACKUP LOG ' + @dbName + 
               ' TO DISK = ''C:\DbBackups\' + @databaseFileName + '_Log_' + @dateTime + '.TRN''' 
        
       EXECUTE sp_executesql @sqlCommand 
END

The above script creates a new stored procedure called Sp_BackupDb (hate on me for my use of bad stored procedure naming another day!) that runs against your database of choice, and depending on what backup type specified it does either a full (using the F parameter), difference (using the D parameter) or log backup (using the L paramter) to a folderC:\DbBackups (feel free to change this to your choice of backup folder).

Run the above script to create the stored procedure

Create a Directory called C:\BackupScripts

Within this new directory create a new SQL script file and name it BackupDatabases.sql

Copy the following script into this SQL script file and add all the database names you want to backup regularly. remember you have the option of using the different backup types i specifiedF, D, L

sp_BackupDatabase 'master', 'F'
GO
sp_BackupDb 'model', 'F'
GO
sp_BackupDb 'msdb', 'F'
GO
sp_BackupDb '[myDatabase1]', 'F'
GO
sp_BackupDb '[myDatabase2]', 'F'
GO
QUIT

Open the Task Scheduler and select Create Basic Task from the right hand menu.

Enter a task name that you want to call your backup i.e. “Daily Database backup” and clickNext

Specify the frequency that you would like the backup to run and click Next

Then enter the start date and time etc. that you want the backup to run (i.e. mine is at midnight every day) and click Next

Specify that you want to Start a program and click Next

Enter the location of your SQLCMD.EXE runtime – depending on your version of SQL Express:

SQL Express 2005 C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE
SQL Express 2008 C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE

Enter the arguments as being:

-S .\sqlexpress -E -i C:\DbScripts\BackupDatabases.sql

This relates directly to:

-S (this specifies the instance name for SQL Server - if you are running this locally this is by default .\sqlexpress)
-E (this specifies that you want to make a trusted connection)
-i (this specifies the input command file which in our case is C:\BackupScripts\BackupDatabases.sql (this is the file that we created above with the command steps)

Click Next

Check the box marked Open the Properties dialog when i click Finish and then ClickFinish

Now click on the button marked Change User or Group next to the security options area of the dialog. This allows us to specify what user the task will run as.