Automating backup of databases in SQL Express.

mssql-logoMicrosoft SQL Server 2005/2008 Express is a free edition of SQL Server that is ideal for learning, developing and powering desktop, web and small server applications, and for redistribution by ISVs.

Top Features

  • Supports stored procedures, triggers, functions, and views
  • Store all kinds of business data with native support for relational data, XML, FILESTREAM and spatial data
  • Improved performance, usability, visualization, in addition to integration with the Microsoft 2007 Office System in SQL Server Reporting Services
  • Simplify development efforts by leveraging existing T-SQL skills, ADO.NET Entity Framework and LINQ
  • Closely integrated with Visual Studio and Visual Web Developer

Because there’s no “SQL Agent” in SQL Express Editions, in order to create scheduled backups on SQL server Express edition, we need to user couple of tricks  :)

First, we need to have a query that will backup all databases to one folder… Well, here it is :)


DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'E:\sqlbackups\' --path to the backup folder
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

This will backup everything except the “Temdb” database. So, lets take a look at the current state of our backup folder.

sqlbackup1

As we can see, only the backups from 21.11.2009 exist. So, what happens when we run the query using SQL Management Studio.

Here’s the query pasted in “New query window”.

sqlquerySooo…if everything went smoothly, we should get a message similar to this one :

sqlquery2Awesome! :) Now, lets take a look at our backup folder, are there any changes? Sure hope there is, because this post will totally suck if the backup didn’t complete :)

sqlbackup2

As we can see, this post will not suck after all :) Notice the new files, created on the 22.11.2009 . IT WORKS! :) All we have to do now is to automate the backup, so we don’t have to run the query everyday by hand.

We will do that using Task Scheduler (or Scheduled tasks in Windows 2003). We have to add a task to execute everyday in 3 AM.

The command we have to run is:

“C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE” -i “c:\pathtosqlquery\sqlbackup.sql”

Where “sqlbackup.sql” is the name of the file you have pasted the code mentioned before.

Here are a couple of pictures so you can see how to do it:

schedule

Now, click on “Create task”. That will click will start the task wizard. Enter the name of the task ( i.e “SQL Backups”.

task1After entering the name and choosing the option “Run wheneter user is logged on or not”, click on “Triggers”.

TASK2

Click on “New”. It will bring up a new window

TASK3Choose the “Daily” option, and set the time when you want the task to run. I have set it to 3 AM .

ONE IMPORTANT THING!!! Dont forget to check the “Enable” checkbox :) , otherwise, you will end up scratching your head and cursing Microsoft and uncle Bill :) hehe.

After that, click on “Actions”, where we will define the program that should be runned… Click on “New”…

TASK4

TASK5Paste this into the “Program/Script” box.

“C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE”

NOTE! – path to the “SQLCMD.exe” may be different on your system!!!

All that is left to do is to “Add arguments”

-i “c:\pathtosqlqueryfile\sqlbackup.sql”

Click on “OK”, enter the system password and thats it. Enjoy your daily backup.

HINT!!!! If you’re lazy like I am, you can use a powershell script to delete backup files older than X days. I have also covered that with a blog post. More info about that on this link.

Bye bye! :)

12 comments

  1. Works very well!

    I modified the SQL Query so that it wouldn’t include the date in the file name.

    At the moment, if the file already exists in the directory the query is appending the new backup to the existing, instead of overwriting it. I can easily move the files out of that location at a designated time as a workaround.

    How would I go about making it so that the query would overwrite existing files instead of appending to them?

    Thank you!

  2. Hi,

    Scripts working fine but not with scheduler.

    Task Scheduler successfully finished “{3555b71d-d506-4646-b469-3ee2cceddc56}” instance of the “\DB Backups” task for user “WEBSERVER\Administrator”.

    But backup is not getting copy on destination.

    Thanks,
    Anuj Kamra

  3. Hi Anuj, I just noticed your comment. Sorry for the delay :D .
    I have the same script running with scheduler on more than 10 machines and everything works flawlessly.

    If you still didn’t manage to make it work, feel free to contact me with any questions you have.

  4. backup script works fine from within SQL server 2008 express and creates the file in the backup destination. Tasks scheduler finishes successfully, but does not create the backup file.

  5. Colin, I have that script running through powershell on lots of machines, some using SQL 2008 Express, some SQL 2012 Express and everything works fine. Can you post your task settings so I can take a look?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>