SQL Server Express - Database Backup Scheduler

Introduction

In this article, we will learn how to create a database backup scheduler in the SQL Express version as it has no built-in auto backup tool like SQL Server. With the help of Task Scheduler (Windows OS) and batch file, we will create a schedular to take the database backup from the SQL Express version in the specified folder.

Requirement

• Batch File
• Stored Procedure (Database Backup) which is not available in SQL Express version
• Configuration on Windows Task Scheduler

Let's get started,

Batch File

Create a batch file as below,

Open notepad and paste the below code and change the parameters as per your configuration and save this file as a .bat extension.

sqlcmd -S .\SQLEXPRESS_Instance_Name -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\Backup\', @databaseName='DatabaseName', @backupType='F'"

In the above statement, sp_BackupDatabases is a stored procedure name that we will create in the next step.

Stored Procedure (Database Backup)

In SQL Express we have to add the below-stored procedure in the default Master database as SQL Express does not provide built-in auto back feature.

We will create the below-stored procedure in the Master database which is a default database provided by SQL Express.

Note
The below-stored procedure is written by Microsoft.

Copied from https://raw.githubusercontent.com/microsoft/mssql-support/master/sample-scripts/backup_restore/SQL_Express_Backups.sql

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 17/07/2021 11:35:01 ******/
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 
-- ============================================= 
ALTER 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= ISNULL(@DatabaseName ,name)
            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] format 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 

Windows Task Scheduler Configuration

Now we will create a windows scheduler to run the batch file to run the above-stored procedure to create an automated backup which is a final step.

Open Task Scheduler as below (type task scheduler from the search bar of windows 10 and press enter).

Create a new task as below,

We will give the name to our scheduler task and select the appropriate option as below. We need to run this scheduler all time so we select this selected option shown below screen.

Now we will set our scheduler - when to run this task using the Trigger tab as below screen and then click on the new button.

Now will create a new trigger to tell the system when to run this task as below. Please configure it as per your requirement.

Once the trigger is set we will now configure the action tab and then click the new button to setup our above-created batch file. We will call our batch file with the help of the action tab as below,

Click on the new button to set the path of our batch file as below,

Once we browse and select the created batch file then click the ok button. And then click on the condition tab to configure it. We will keep default settings as below,

And now final configuration, click on the settings tab as below. Again we will keep default settings as below,

Click the ok button will create your task and will run this task as per your configuration. Note: It will ask to enter windows credentials which have admin rights if you do not have enough rights to create such a task.

Once all set, the scheduler will run the task to call the batch file and that batch file will call the stored procedure to get the backup of mentioned database in the batch file.

Note
Please make sure that the information you mention in the batch is correct.

Happy Coding!!