April 9, 2014

How To Backup A SQL Express Database With A Scheduled Task, And Cleanup Old Backups

So you are running SQL Server (2005 or 2008) Express (presumably installed as part of an application server install) and you would like to make sure your databases are backing up each night. You go to load the SQL Server Management Studio only to discover it’s not there. So then you head on over to Microsoft’s website and download the install for SQL Server 2005 Management Studio Express (2008 here). But then you soon discover there are no maintenance plan options, and the SQL Server Agent is nonexistent. So how do you backup a SQL Express database?

Here’s what Microsoft has to say about the situation:

SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.

Luckily for us they proceed to offer us that “different approach” they mention. In the Microsoft Knowledge Base article (2019698, “How to schedule and automate backups of SQL Server databases in SQL Server Express“), they offer us a stored procedure and several batch file scenarios. Be sure to check out that article to gain a full understanding of all the options. I will offer one of those options here, along with that stored procedure, and then I will also add in a line of batch file code that will allow you to delete older backups, giving you a true replacement for the SQL Agent / maintenance plan method of SQL database backups.

STEP 1

Run the below script (provided by Microsoft) on your SQL Express server (I assume at this point you have the SQL Server Management Studio installed). This will add a stored procedure to your master database.


// Source code (http://support.microsoft.com/kb/2019698)

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
AS

SET NOCOUNT ON;

DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)

-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
AND name=@DatabaseName
OR @DatabaseName IS NULL
ORDER BY Name

-- Filter out databases which do not need to backed up
IF @backupType='F'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
END
ELSE IF @backupType='D'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE IF @backupType='L'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE
BEGIN
RETURN
END

-- Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int

-- Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs

WHILE @Loop IS NOT NULL
BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name
SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

-- Create backup filename in path\filename.extension format for full,diff and log backups
IF @backupType = 'F'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'D'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'L'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media
IF @backupType = 'F'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
IF @backupType = 'D'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
IF @backupType = 'L'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

IF @backupType = 'F'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'D'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'L'
BEGIN
SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END

-- Execute the generated SQL command
EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

 

STEP 2

Create a batch file with the code below (edit to match your environment):

sqlcmd -S .\INSTANCE -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackup\', @backupType='F'"

forfiles /P "D:\SQLBackup" /S /M *.BAK /D -14 /C "cmd /c del @PATH"

This batch file will perform a FULL backup of ALL databases in the specified SQL instance to D:\SQLBackup. It will delete backups that are older than 14 days.

The first line is the code that performs the backup. “SQLCMD” should be in the path, if not, it’s usually located here: \Program Files (x86)\Microsoft SQL Server\90\Tools\Binn

The second line is the code that performs the cleanup, deleting older backup files. This specific “forfiles” code works on Windows Server 2008 R2, but it’s my understanding that Microsoft was not consistent with the command line options for this command. So your mileage may vary.

Be sure to replace “.\INSTANCE” with the name of the instance on your SQL Express server. I “think” if you specify “.\” without the quotes it will use the default instance, but I have not tested that. In my case, the application install created a unique instance name, and that is what I specified in my batch file.

And replace both occurrences of “D:\SQLBackup\” with the location you want to backup to, and change the value specified after the “/D” switch in the “forfiles” command to the amount of days you want to keep backups. In the example above, 14 days of backups will be kept (assuming the backups are done each day).

 

STEP 3

Schedule it!

  1. Create a new scheduled task in Task Scheduler and call it something like “SQL Express Backup.”
  2. Set up a Trigger that executes the task every day at a specified time.
  3. Set up an Action that runs your batch file (wherever you put it, I recommended just keeping it in the backup location).
  4. Configure the task to run with a user account that has full (or at least read/write/modify) access to the database backup path, and has full access to the SQL databases (or at least assigned to the “BackupOperator” role).

 

Now your SQL Express databases should backup each day, and the oldest backup will roll off after each new backup.

Please share your thoughts