Home > Backup and Recovery > Quick and easy way to automate SQL Express database backups

Quick and easy way to automate SQL Express database backups

One of free SQL Server Express limitations is the lack of SQL Server Agent and, consequently, Maintenance Plans as such.

The easy way to automate regular database tasks, such as database backup would be to use internal windows scheduler and just a simple “ms dos”-like batch file.

First, the backup stored procedure has to be created in [master] database.


CREATE PROCEDURE Sp_dbbackup_user_and_system_databases

AS

BEGIN

DECLARE @db_bkp_location NVARCHAR(50)

DECLARE @today NVARCHAR(10)

DECLARE @bkpsql NVARCHAR(500)

DECLARE @db_name NVARCHAR(50)

DECLARE bkpcur CURSOR FOR

SELECT NAME

FROM   sys.databases

WHERE  NAME NOT IN ( ‘tempdb’ )

ORDER  BY NAME

SET @db_bkp_location = ‘E:\DBBackup’

SET @today = CONVERT(VARCHAR, Getdate(), 112)

SET @today = Substring(@today, 1, 4) + ‘_’

+ Substring(@today, 5, 2) + ‘_’

+ Substring(@today, 7, 2)

SET @bkpsql = 

SET @db_name = 

OPEN bkpcur

FETCH bkpcur INTO @db_name

WHILE @@fetch_status = 0

BEGIN

SET @bkpsql = ‘BACKUP DATABASE ‘ + @db_name + ‘ TO  DISK = ‘

+ ”” + @db_bkp_location + ‘\’ + @db_name + ‘_’ +

@today

+ ‘.bak’ + ””

+ ‘ WITH NOFORMAT, INIT,  NAME = ‘ + ””

+ @db_name + ‘-‘ + @today + ‘-Full Database Backup’

+ ”” + ‘, SKIP, NOREWIND, NOUNLOAD’;

–print @bkpsql

EXECUTE Sp_executesql

@bkpsql

FETCH bkpcur INTO @db_name

END

CLOSE bkpcur

DEALLOCATE bkpcur

END

Now the batch file

@echo off

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC master..sp_DBBackup_User_and_System_Databases”

Now what’s left is just to schedule it with standard windows scheduler.

Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks -> Add Scheduled Tasks, plug in the batch file name, pick up the recurrence you want and it’s good to go. Like it was one of those Maintenance Plan tasks or just plain SQL Agent jobs we’re all used to.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: