Scheduled Maintenance Plan for SQL Express DBs

Automate Database backup with SQL Express using scripts and batch schedule

Let us automate database backup in SQL Express with mixed Windows and SQL Authentication running on Windows 2008 Server.   This material was tested with Microsoft SQL Server 2008 R2 Express and SQL server 2005 Express Edn.

SQL Server 2008 Enterprise comes with Maintenance Plan feature.  SQL Express edition lacks this feature.  But you still need a backup.

Backup automation with SQL Express can be carried out by following the steps:

i) Create the script in sql to backup all your databases.

ii) Execute the script by initiating it from a command prompt (SQLCMD command).

iii) Schedule the script using Windows Task Scheduler.

i)

-- script starts here

DECLARE @dbName        VARCHAR(33)    -- database name

DECLARE @path          VARCHAR(99)    -- backup path

DECLARE @fileName      VARCHAR(99)    -- backup file name

DECLARE @fileQuarter   CHAR(1)        -- variable portion of file name

SET @path = '\\192.168.16.193\backup\TestDB\Bkp\' 

-- the above path is a remote location. make sure path is changed according to the environment you may have.

-- Returns a Numeral from 1 to 4

-- Each Quarter you get a new backup file name

SELECT @fileQuarter = CONVERT (char(1),(MONTH(GETDATE())+2)/3, 112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

-- Exclude all System Databases, if Needed - 'master','model','msdb'

WHERE name NOT IN ('tempdb') 

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbName  

WHILE @@FETCH_STATUS = 0

BEGIN

       SET @fileName = @path + @dbName + '_' + @fileQuarter + '.BAK'

       -- Use INIT and SKIP to overwrite previous sets

       BACKUP DATABASE @dbName TO DISK = @fileName  WITH INIT, SKIP

       FETCH NEXT FROM db_cursor INTO @dbName

END

CLOSE db_cursor

DEALLOCATE db_cursor

-- script ends here

--save the above script in .sql format (Ex. Bkp.sql)

ii) Create a windows batch file with .bat extension (Ex. bkp.bat) the content of which is as follows:

SQLCMD -E –S TST-APP1\myDB -i "c:\yourlocalfolder\Bkp.sql" -o "c:\yourlocalfolder\Log.txt"

iii) Now create a scheduled task from windows control panel. Make a schedule to run the batch file 'bkp.bat' file on a daily or weekly basis.

It is wise to take the same backup to a tape drive as per the backup plan: daily, weekly and monthly so that you don't leave a question or thought.