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,http://opensource.org/licenses/ms-pl.html.)
4 USE [master]
5 GO
6 /****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
7 SET ANSI_NULLS ON
8 GO
9 SET QUOTED_IDENTIFIER ON
10 GO
11  
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 -- =============================================
20  
21 CREATE PROCEDURE [dbo].[sp_BackupDatabases]
22  @databaseName sysname = null,
23  @backupType CHAR(1),
24  @backupLocation nvarchar(200)
25 AS
26  
27  SET NOCOUNT ON;
28  
29  DECLARE @DBs TABLE
30  (
31  ID int IDENTITY PRIMARY KEY,
32  DBNAME nvarchar(500)
33  )
34  
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
37  INSERT INTO @DBs (DBNAME)
38  SELECT Name FROM master.sys.databases
39  where state=0
40  AND name=@DatabaseName
41  OR @DatabaseName IS NULL
42  ORDER BY Name
43  
44  -- Filter out databases which do not need to backed up
45  IF @backupType='F'
46  BEGIN
47  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
48  END
49  ELSE IF @backupType='D'
50  BEGIN
51  DELETE @DBs where DBNAME IN('tempdb','Northwind','pubs','master','AdventureWorks')
52  END
53  ELSE IF @backupType='L'
54  BEGIN
55  DELETE @DBs where DBNAME IN('tempdb','Northwind','pubs','master','AdventureWorks')
56  END
57  ELSE
58  BEGIN
59  RETURN
60  END
61  
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
69  
70  -- Loop through the databases one by one
71  SELECT @Loop = min(ID) FROM @DBs
72  
73  WHILE @Loop IS NOT NULL
74  BEGIN
75  
76 -- Database Names have to be in [dbname] formate since some have - or _ in their name
77  SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
78  
79 -- Set the current date and time n yyyyhhmmss format
80  SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
81  
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'
89  
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
97  
98 -- Generate the dynamic SQL command to be executed
99  
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
112  
113 -- Execute the generated SQL command
114  EXEC(@sqlCommand)
115  
116 -- Goto the next database
117 SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
118  
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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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