Stephen's Blog

Automate SQL Server Express Backups

This article was written in 2008. Some or all of the information and views may be out of date. Please keep this in mind while reading.

At my place of work and at home I currently use SQL Server Express 2005 to develop my web applications before they go live with the full blown version of SQL Server. An arrangement which works just fine... however.

I have never really backed up the databases on a regular basis. I generally make the odd manual backup now and again when I'm working on them, but it's by no means the best method. Unfortunately, SQL Server Express does not come with the SQL Server Agent which is used in the full version to automate tasks, including backups, and this presents a problem when you want to automate anything in Express. Luckily, there is a solution.

A website called LazyCoding has created a excellent (free) product called SQL Scheduler, which runs a service in the background and fires off SQL commands according to the jobs specified in the GUI Tool. The installation process is a manual job, but heck there are only 3 steps to installing it, any DBA or Developer should be able to do it. Now all I need is a script that backs up all of my SQL Express databases to a folder on my server.

With thanks to the Author of this little gem, I have produced this slightly altered version to suit my specific case which I now present:

Use Master

Declare @DatabaseName sysname
Declare @SQLCommand varchar(1024)
Declare curDBName Cursor For
Select [Name] From Master..Sysdatabases
Where [Name] Not In ('tempdb')

Open curDBName
Fetch curDBName Into @DatabaseName

While (@@fetch_status = 0)

Begin
       
if databasepropertyex (@DatabaseName,'Status') = 'online'
       
Begin
               
Select @SQLCommand = 'Backup Database ' + @DatabaseName +
               
' To Disk = ''D:\Backups\Databases\' + @DatabaseName +
                '
.bak'' With Format, Init'
                execute (@SQLCommand)
        End
        Fetch curDBName Into @DatabaseName
End

Close curDBName
Deallocate curDBName

If the above Cursor method looks a bit OTT for what I'm wanting to achieve, then this much smaller version is perhaps what is needed. Thanks goes to Moonshield for this suggestion.

Use Master

Declare @ToExecute VarChar(8000)
Select @ToExecute = ''

Select @ToExecute = Coalesce(@ToExecute + 'Backup Database ' + [Name] + ' To Disk = ''D:\Backups\Databases\' + [Name]   + '.bak'' With Format;' + char(13),'')
From
Master..Sysdatabases
Where
[Name] Not In ('
tempdb')
and databasepropertyex ([Name],'
Status') = 'online'

Execute(@ToExecute)

You can now create a new job using the GUI Tools in the same way you would with the full version of SQL Server.

On Monday the 27th October 2008, I updated the second function to include the line, Select @ToExecute = '', which stops the bug that the first database is missed.

You cannot backup the database tempdb.

Comments