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).

1 -- Copyright © Microsoft Corporation. All Rights Reserved.
2 -- This code released under the terms of the
3 -- Microsoft Public License (MS-PL,
4 USE [master]
5 GO
6 /****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
8 GO
10 GO
12 -- =============================================
13 -- Author: Microsoft
14 -- Create date: 2010-02-06
15 -- Description: Backup Databases for SQLExpress
16 -- Parameter1: databaseName
17 -- Parameter2: backupType F=full, D=differential, L=log
18 -- Parameter3: backup file location
19 -- =============================================
21 CREATE PROCEDURE [dbo].[sp_BackupDatabases]
22  @databaseName sysname = null,
23  @backupType CHAR(1),
24  @backupLocation nvarchar(200)
25 AS
30  (
32  DBNAME nvarchar(500)
33  )
35  -- Pick out only databases which are online in case ALL databases are chosen to be backed up
36  -- If specific database is chosen to be backed up only pick that out from @DBs
38  SELECT Name FROM master.sys.databases
39  where state=0
40  AND name=@DatabaseName
41  OR @DatabaseName IS NULL
42  ORDER BY Name
44  -- Filter out databases which do not need to backed up
45  IF @backupType='F'
47  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
48  END
49  ELSE IF @backupType='D'
51  DELETE @DBs where DBNAME IN('tempdb','Northwind','pubs','master','AdventureWorks')
52  END
53  ELSE IF @backupType='L'
55  DELETE @DBs where DBNAME IN('tempdb','Northwind','pubs','master','AdventureWorks')
56  END
57  ELSE
60  END
62  -- Declare variables
63  DECLARE @BackupName varchar(100)
64  DECLARE @BackupFile varchar(100)
65  DECLARE @DBNAME varchar(300)
66  DECLARE @sqlCommand NVARCHAR(1000)
67  DECLARE @dateTime NVARCHAR(20)
68  DECLARE @Loop int
70  -- Loop through the databases one by one
71  SELECT @Loop = min(ID) FROM @DBs
76 -- Database Names have to be in [dbname] formate since some have - or _ in their name
79 -- Set the current date and time n yyyyhhmmss format
80  SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
82 -- Create backup filename in path\filename.extension format for full,diff and log backups
83  IF @backupType = 'F'
84  SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME,'[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
85  ELSE IF @backupType = 'D'
86  SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME,'[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
87  ELSE IF @backupType = 'L'
88  SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME,'[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
90 -- Provide the backup a name for storing in the media
91  IF @backupType = 'F'
92  SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
93  IF @backupType = 'D'
94  SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
95  IF @backupType = 'L'
96  SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
98 -- Generate the dynamic SQL command to be executed
100  IF @backupType = 'F'
101  BEGIN
102  SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
103  END
104  IF @backupType = 'D'
105  BEGIN
106  SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
107  END
108  IF @backupType = 'L'
109  BEGIN
110  SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
111  END
113 -- Execute the generated SQL command
114  EXEC(@sqlCommand)
116 -- Goto the next database
117 SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
119 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:

1 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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s