SQL Server 2008 Express backup script

Recently I was asked to install SQL Server for a new project. I was not able to get a hold of the company key for SQL Server 2008 (don’t get me started on that), so I had to install the Express Edition. Unfortunately the SQL Server 2008 Express Edition does not provide a simple way to configure backups like I recall the regular version providing (note: I’m not a SQL Server DBA). As a result I had to write a script and create a scheduler job to perform the backup of my databases.

I found some scripts online, but none worked out of the box and most used a counter loop than when implemented resulted in multiple backups of a database that had no prior database in the ID order. This is the solution I arrived at. It has been working quite well and I was able to destroy one of my databases (intentionally) and recover from this backup.

Note: I didn’t bother to fix the one failure condition I recognized. If you drop the database with the highest ID while this script is running it’ll loop infinitely backing up the last one it reaches.

-- SQL Server backup routine to backup all databases
-- Jed S. Walker, 2011/10/03
-- to automate via job scheduler
--   sqlcmd -S theSQLserver\theWeather -i C:\SQLSERVERBACKUP\scripts\backup_db_all.sql -o C:\SQLSERVERBACKUP\backup_db_all.log

PRINT '=>Setup base variables'
DECLARE @today VARchar(20)
DECLARE @dbname nvarchar(255)
DECLARE @backupname nvarchar(255)
DECLARE @backuploc nvarchar(255)
SET @backuploc = 'C:\SQLSERVERBACKUP\'  
PRINT @backuploc
--SET @today = CONVERT(CHAR(8), GETDATE(), 112)
SET @today = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 126),'-',''),':',''),'.','')
PRINT @today

---- backup all online databases
-- first get all databases to be backed up
DECLARE @dbList TABLE
( dbid INT,
  dbname NVARCHAR(256)
)
INSERT  INTO @dbList ( dbid, dbname )
SELECT  dbid, name
FROM    master.dbo.sysdatabases
WHERE   ( name NOT IN ( 'tempdb' ) )
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
ORDER BY dbid

------ Starting backup, one by one
DECLARE @maxid int;
DECLARE @curid int;
SELECT @curid = MIN(dbid)-1, @maxid = MAX(dbid) from @dbList;

PRINT '=> Perform backups'
WHILE @curid != @maxid
  BEGIN
    SELECT  TOP(1)
            @curid = dbid,
            @dbname = dbname
    FROM    @dbList
    WHERE   dbid > @curid;
    PRINT 'Debug: curid= ' + str(@curid) + ' maxid=' + str(@maxid)
    SET @backupname = @backuploc + @dbname + '_' + @today + '.bak'
    PRINT N'=> Backup: ' + @dbname + ' to ' + @backupname
    BACKUP DATABASE @dbname TO DISK = @backupname;
  END

PRINT ''
PRINT '=>Done with backups'

Then just use the Microsoft Windows Task Scheduler to schedule the job with a command like this:

sqlcmd -S theSQLserver\theWeather -i C:\SQLSERVERBACKUP\scripts\backup_db_all.sql -o C:\SQLSERVERBACKUP\backup_db_all.log

Enjoy

One response to “SQL Server 2008 Express backup script

  1. Hi there, I enjoy reading all of your post. I wanted to
    write a little comment to support you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s