Scheduling database backups in SQL Server 2008R2 Express

The latest version of SQL Server Management Studio (SSMS) doesn’t have the SQL Server Agent that enables you to schedule jobs. I guess that’s a “paid for” extra!

So to back up your databases, you need to create a T-SQL script and run it through Task Scheduler.

1: Create stored procedure in ‘master’ database

Create a new query in SSMS, copy & paste the code below, and run it (F5).

-- Copyright © Microsoft Corporation. All Rights Reserved. -- This code released under the terms of the -- Microsoft Public License (MS-PL, 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

I personally removed the dates from the backup name, so that I only ever have one backup file which gets replaced every time the script runs.

2: Create .bat file to call the procedure

Now you need to write a command that will run the procedure, and save it in a file that the task scheduler can use.

Create a new text file, and save it as sqlbackup.bat – if your machine is set to hide file extensions either change the settings so that you can see/change them, or use a text editor that can save a .bat file.

Enter the following script:

sqlcmd -S YOUR_SQL_SERVER_NAME -Q "EXEC sp_BackupDatabases @databaseName='YOUR_DATABASE_NAME', @backupLocation='C:\Your\Folder\Path', @backupType='F'"

In the above replace:

  • YOUR_SQL_SERVER_NAME: typically .\SQLEXPRESS or SERVERNAME\SQL_SERVER_INSTANCE – you can see what it is as the SSMS login screen
  • YOUR_DATABASE_NAME: the name of the database in SQL Server. Alternatively you can remove the @databaseName variable entirely to backup all databases in SQL Server
  • The folder path where you want to save the backup

You can also set the @backupType variable to:

  • ‘F’ for full backup
  • ‘D’ for differential backup
  • ‘L’ for log file backup

Test the command

Test that your command works by saving the .bat file, then double clicking it to run it. A Windows Command Line window will open whilst the script is executing. When completed you should be able to see a new .bak file in the folder you specified.

3: Create the schedule

  1. Open task scheduler from Start >> Administrative Tools >> Task Scheduler
  2. Click “Create Basic Task” and enter a relevant name, e.g. Daily SQL Backup
  3. Under “Triggers” select ‘Daily’ and then choose a time to run the backup.
    Try to choose a time when your database traffic is low, typically in the early hours of the morning. Also make sure that it doesn’t overlap with other scheduled tasks like Windows Updates that may reboot the server.
  4. Under “Action” select ‘Start a program’ and browse to the script you created earlier

Your database(s) will now be being backed up on a daily basis. This is always a good thing!

In my next post I’ll take a look at how to get a free offsite database backup, using 7zip, Dropbox and Task Scheduler.