Lucee Developer

I finally managed to fully automate the backups for my sql server 2005 express edition databases. The product is free, and therefor great. But the limitations seem to be the jobs you can't add, and the scheduling that isn't available.

So, I made this small but handy Stored procedure, which queries all database names, loops through them, and creates a backup file for each of them:

CREATE PROCEDURE bakall
AS

    /*Declare the variables that we will need in the stored proc */
    Declare @count Int
    Declare @LoopCount Int
    Declare @dbname Varchar(50)
    Declare @bakpath Varchar(200)
    
    SELECT name 
    into #temp
    FROM sys.databases
    where name not in  ('master', 'tempdb', 'model', 'msdb')
    
    
    select Identity(int,1,1) id, [name] as dbname
    into #temp2
    from #temp
    
    set @count = @@rowcount
    set @LoopCount = 1
    
    while @LoopCount <= @count
    begin
        select @dbname = dbname
        from #temp2
        where id = @LoopCount
    
        set @LoopCount = @LoopCount + 1
        set @bakpath = 'E:\daily bak db\' + @dbname + '.bak'
        
        BACKUP DATABASE @dbname TO  DISK = @bakpath WITH NOFORMAT, NOINIT,  NAME = 'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    end

Thanks to this blog for pointing out the looping over a recordset.

The real problem was the scheduling, since sql server doesn't seem to support it.
So, I created a coldfusion script  to call the procedure in the db:

<cfquery name="q" datasource="#application.dbs#">
    exec bakall
</cfquery>

Unfortunately, that didn't exactly work out. the user which is set for the datasource "#application.dbs#" only has rights in one database, so errors were thrown while trying to call BACKUP with opther databases.
So I went back to sql server management studio, and added "db_backupoperator" rights for each database on the server for the given user.

Then, finally, it worked :-)

After that, I made sure Cobian backup (which rocks!) is backing up the bak files to an external location, and now, i can finally relax!