Automate Database Backup with SQL Express

This article describes, how to 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.

SQL Server 2008 Enterprise comes with Maintenance Plan feature.  That feature allows amazing flexibility and auditing.  SQL Express edition lacks this feature.  But you still need a backup.

Backup automation with SQL Express consist of 3 steps:

(1) Write the script to backup all your databases.

(2) Test the script by initiating it from a command prompt.

(3) Schedule the script using Task Scheduler.

Here is each step in detail.

`

(1) Write the Script

This script overwrites any previous backup sets found on the disk with the same name.

If you give your script a *.SQL extension, its default editor will be a colorful SQL Manager.

You can schedule this script to run every day, but after a year of running  your will have 4 different backup sets to go back to.

This scripts creates 4 different backup files every year. Let’s say you are in April of 2011.  Your latest back will be stored in YourDB2.BAK file, but you could go back as far as last backup in 2010 third quarter stored in file YourDB3.BAK.

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 = 'C:\_BACKUP\SQL\'  

-- 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 

`

(2) Test the script

To test the script you need to exit from SQL Server Studio.

In the command bellow SQL_Instance is usually SQLEXPRESS.

SQLCMD -E -S .\SQL_Instance -i X:\_BACKUP\SQL\_Backup.sql -o X:\_BACKUP\SQL\Log.txt

You should see multiple outputs stating “BACKUP DATABASE successfully processed …”

If you see an error message:

Sqlcmd: Error: Connection failure. SQL Native Client is not installed correctly.

it might mean that an SQL Server Agent service is disabled for your SQL instance.  First, try to enabling the service and starting it. If this doesn’t work, uninstall Microsoft SQL Server2008 Native Client and run SQL setup again. Try to start the service again.

`

(3) Schedule the Script

Run taskschd.msc and schedule a new task.  You can schedule to run this script every day.  Entire third quarter the script will overwrite YourDB3.BAK file.

Surprisingly that step also took a lot of time and fine tuning to begin to work.  First of all, I had to go back to previous command and add options -E and -o.  Secondly, it still wouldn’t  with return error 1.  So, if you are getting any problems with this step, try 3 things:
– start Task Scheduled as an Administrator
– Pick option Run whether user is logged on or not and
– Check Run with highest privileges check box

Note: you have to exclude ‘tempdb’ because otherwise you will keep getting this error message:

Msg 3147, Level 16, State 3, Server Server\Instance, Line 24
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Server Server\Instance, Line 24
BACKUP DATABASE is terminating abnormally.