Scheduled backup on a SQL Express 2005

I needed to create scheduled backups of a SQL Express 2005 instance for an application. TO be able to do this you can use the sqlcmd.exe utility that are installed with SQL Express.

To create a scheduled backup you need to do the following:

1. Create (and insert) a new stored procedure into SQL Express
2. Create a batch file
3. Create a scheduled task for running the batch file

Create the stored procedure

1. Copy the code below into notepad and save the file as C:\DB.sql:
   

   
   USE [master]
   GO
   /****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   -- =============================================
   -- Author: Microsoft
   -- Create date: 2010-02-06
   -- Description: Backup Databases for SQLExpress
   -- Parameter1: databaseName
   -- Parameter2: backupType F=full, D=differential, L=log
   -- Parameter3: backup file location
   -- =============================================
   CREATE PROCEDURE [dbo].[sp_BackupDatabases]
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200)
   AS
       SET NOCOUNT ON;
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000)
            DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
      WHILE @Loop IS NOT NULL
      BEGIN
   -- Database Names have to be in [dbname] formate since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
   -- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
   -- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
   -- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
   -- Generate the dynamic SQL command to be executed
       IF @backupType = 'F'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'      
                  END
       IF @backupType = 'L'
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'      
                  END
   -- Execute the generated SQL command
       EXEC(@sqlCommand)
   -- Goto the next database
   SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
   END
   

2. Open an administrative command prompt
3. Run the following command: sqlcmd –S .\SQLInstanceName –i C:\DB.sql

Create the batch file

1. Open notepad
2. Enter the following:
   

   REM sqlbackup.bat
   sqlcmd -S .\SQLInstanceName -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackup\', @backupType='F'"

   
   
3. Save the file to disk (for example C:\Full-DB-backup.cmd)

Create a scheduled task

• Open Scheduled task manager
• Create a new task (run it as the user that installed SQL Express)
• Point to the batch file created earlier (C:\Full-DB-backup.cmd)
• Set to run as needed (hourly, daily, weekly)
  

If you get an error message “The following error was reported: A specified logon session does not exist. It may already have been terminated” when running the scheduled task you need to modify the following security setting to allow storing passwords (gpedit.msc or with a GPO):

Computer > [Policies] > Windows Settings > Security Settings > Local Policies > Security Options > Network access: Network access: Do not allow storage of credentials or .NET Passports for network authentication

Links

How to schedule and automate backups of SQL Server databases in SQL Server Express Editions [Microsoft]
Automating Database maintenance in SQL 2005 Express Edition [SQLdbatips.com]
Task Scheduler – A Specified Logon Session Does Not Exist