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 
Reference:
Simple script to backup all SQL Server databases http://www.mssqltips.com/tip.asp?tip=1070
Using the sqlcmd Utility http://msdn.microsoft.com/en-us/library/ms180944.aspx

`

(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.
(Visited 5 times, 1 visits today)

6 Comments

  1. i also like sqlbackupandftp.com for it features to save backups over FTP and into Dropbox or Amazon S3 cloud storage.

  2. Udar,
    SQLBackupAndFTP also allows to backup ALL databases (see the checkbox on the main form). If you add a new database – you don’t need to do anything. Even in free version it adds FTP, email confirmations, diff and tran log backups and custom schedule.
    So practically speaking there’s just no reason to use a custom script if you approach this task as a business. If it’s for fun – then it’s another story.

  3. REPLY FROM THE AUTHOR: This solution allows to backup all databases in your SQL instance.
    If you add more user databases, you do not need to change anything in SQL backup script or in a scheduled task. One more time: this solution backs up ALL databases in your SQL instance.

  4. I used a similar script to run daily backups, but when we grew up to more than one database to backup I had to come up with an “easier to maintenance” process.

    The solution was this tool http://sqlbackupandftp.com/ that let you schedule backups for more than one database at a time.

  5. Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!

2 Trackbacks / Pingbacks

  1. Moving a IIS WordPress Site to Amazon EC2 | Domain Web Center
  2. My Homepage

Your question, correction or clarification Ваш вопрос, поправка или уточнение

Editor on duty will review your comment. Can't wait? Discuss your topic right now in forums, if you register and login.     Required fields are marked with * Ваше послание пойдет на просмотр в редакцию. Не можете ждать? Обсудите ваш вопрос прямо сейчас в форуме Русский Круг , ecли вы зарегистрируетесь и войдёте под своим именем.     Поля, обязательные для заполнения, помечены *
* *