Archive

Posts Tagged ‘SQL Server Express backup’

Quick and easy way to automate SQL Express database backups

August 6, 2015 Leave a comment

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.