Some techy stuff and occasional verbose ramblings :D

Servers, Windows and Powershell

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
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
FETCH NEXT FROM db_cursor INTO @name
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.


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 🙂


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:


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


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


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


  1. KristofM

    Working great!!! Thanks for sharing this with us.

  2. Alesandro Slepčević

    Great! 🙂 Glad it helped!

  3. ben

    Hi – this is great – it will back up triggers associated with the database, right?Thanks

  4. JB

    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!

  5. Alesandro Slepčević

    Yes, the date part was in to prevent appending the backups ( since I needed daily backups ). Will this help you?

  6. Alesandro Slepčević

    Yes, it should backup everything, since it does a Full backup.

  7. Diogo Maia

    How it works if you dont invoque de SQL instance???

    Use parameter -S server\instance

  8. Vivek


    Thanks a lot it helped me lot … 🙂

  9. Anuj


    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.

    Anuj Kamra

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Theme by Anders Norén