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! :)