Automated Backup from SQL 2005 Express server

On a customers site we moved an application database from the central SQL server to the same server that also hosts this application back-end. The application vendor suggested this step due to performance issues we are experiencing during the data import process.

The customer did not have enough SQL licenses to support a second full SQL installation, so to make this work we have installed the free SQL 2005 Express version. This works fine and the application performance during the import process and during working hours has improved. A down side for using the free version of SQL server is that Database backups can’t be performed from the SQL management suite.

With some help from the all mighty Google I wrote this little SQL script to make the backup work anyway.

DECLARE @BackupDBFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile

varchar(50)
  DECLARE @FileName varchar(30), @MediaName varchar(30), @BackupDir nvarchar(200)

SET @BackupDir = 'D:\Backuped_SQL_DB\'

--Add a list of all databases not to be included in the backup

DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND

name <> 'model' AND name <> 'Northwind' AND name <> 'master' AND name <> 'msdb'
  OPEN Database_Cursor

FETCH next FROM Database_CURSOR INTO @DB

WHILE @@fetch_status = 0

    BEGIN
          SET @FileName = @DB + '( Daily BACKUP )'

        SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)

        SET @BackupDBFile = @BackupDir + + @DB + '_' + 'Full' + '_' +

            CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'

        SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) +

'.'

        IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB =

'master'
              BEGIN

                SET @BackupDBFile = @BackupDir + @DB + '_' + 'Full' + '_' +

                    CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'

                SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar,

CURRENT_TIMESTAMP) + '.'
              END            ELSE

            BEGIN

                SET @BackupDBFile = @BackupDir + @DB + '_' + 'Full' + '_' +

                    CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'

                --SET some more pretty stuff for sql server.

                SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar,

CURRENT_TIMESTAMP) + '.'
              END

            BACKUP DATABASE @DB TO DISK = @BackupDBFile

            WITH NAME = @FileName, DESCRIPTION = @Description ,

            MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,

            STATS = 10

        FETCH next FROM Database_CURSOR INTO @DB

END

CLOSE Database_Cursor

DEALLOCATE Database_Cursor

The next thing you need to do is schedule this script to make the backup on regular intervals. I used the following code to execute the SQL script with the Windows scheduler.

This works fine! The backup files are copied to the central database server so they are included in the full backup of the network. The next problem you’re facing is that the D drive of your server is filling up with backup files. To fix this problem I wrote a cleanup script to preserve only the two most recent backup files and delete the older files.

ECHO ONREM Delete SQL Backup files older than 2 days
FORFILES /p D:Backuped_SQL_DB /s /m *.* /d -2 /c "CMD /C del /Q @FILE

Now we have an backup from the application database and we saved a little money, plus I had a lot off geeky fun creating these scripts..