Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Scheduling Backups for SQL Server Express


By:   |   Read Comments (38)   |   Related Tips: More > Express Edition

Problem

One problem with SQL Server Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.

Solution

There are two components to this; the first is the backup command and the second is the scheduling needed to run the backups.

SQL Server Backup Commands

There are a few things that we need to setup. The first is to create a stored procedure that allows us to dynamically generate the backup file name as well as what type of backup to run Full, Differential or Transaction Log backup. The default for this stored procedure is to create the backups in the "C:\Backup" folder. This can be changed to any folder you like.

The following stored procedure should be created in the master database. This is just one way of handling this. There are several other options and enhancements that can be made.

USE [master]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  


-- =============================================  
-- Author: Edgewood Solutions  
-- Create date: 2007-02-07  
-- Description: Backup Database  
-- Parameter1: databaseName  
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================  
CREATE PROCEDURE [dbo].[sp_BackupDatabase]   
       @databaseName sysname, @backupType CHAR(1)  
AS  
BEGIN  
       SET NOCOUNT ON;  

       DECLARE @sqlCommand NVARCHAR(1000)  
       DECLARE @dateTime NVARCHAR(20)  

       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +  
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   

       IF @backupType = 'F'  
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +  
               ' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''  
         
       IF @backupType = 'D'  
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +  
               ' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'  
         
       IF @backupType = 'L'  
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName +  
               ' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''  
         
       EXECUTE sp_executesql @sqlCommand  
END 

The second part of this is to create a SQLCMD file to run the backup commands. Here is a simple SQLCMD file that backups databases master, model and msdb.

This file gets saved as backup.sql and for our purposes this is created in the "C:\Backup" folder, but again this could be put anywhere.

sp_BackupDatabase 'master', 'F'
GO
sp_BackupDatabase 'model', 'F'
GO
sp_BackupDatabase 'msdb', 'F'
GO
QUIT

Scheduling SQL Server Backups

Included with the Windows operating system is a the ability to setup and run scheduled tasks. This is generally not used for SQL Server environments, because SQL Server Agent is so robust and gives you a lot more control and options for setting up re-occurring jobs. With SQL Server Express the only choice is to set a scheduled task at the operating system level or look for some third party tool.

To setup a scheduled task you need to open the folder where you can create a new scheduled task. This can be found under Accessories -> System Tools -> Scheduled Tasks or under Control Panel.

add scheduled task

The first thing to do is to click on "Add Scheduled Task" and the following wizard will run.

scheduled task wizard

Select the application that you want to run. For our purposes we will be using SQLCMD.EXE. In order to find SQLCMD.EXE you will need to click on the Browse... button.

You should be able to find this in the following directory "C:\Program Files\Microsoft SQL Server\90\Tools\Binn".

browse to find sqlcmd.exe

select sqlcmd.exe

Give the scheduled task a name and specify when to perform the task.

give task name

Specify the time that this should be run.

select schedule time

Provide the credentials for the account that will run this task.

provide credentials

Finish and save the task. One thing you want to do is click on the "Open advanced properties" so you can edit the command.

complete scheduled task setup

Below is the advanced properties screen. You will need to change the "Run" command to the following:

sqlcmd -S serverName -E -i C:\Backup\Backup.sql

This is broken down as follows:

  • sqlcmd
  • -S (this specifies the server\instance name for SQL Server)
  • serverName (this is the server\instance name for SQL Server)
  • -E (this allows you to make a trusted connection)
  • -i (this specifies the input command file)
  • C:\Backup\Backup.sql (this is the file that we created above with the command steps)
review task setup

That should do it. The scheduled task should now be setup.

If you want to run the command now to make sure it works go back to the Scheduled Tasks view and right click on the task and select "Run".

run scheduled task
Next Steps
  • Although this is a pretty simple example this should allow you to backup your SQL Server databases pretty easily
  • Modify the process to handle errors and also to take other parameters
  • Also take a look at this tip, Free Job Scheduling Tool for SQL Server Express and MSDE, to see if this tool makes more sense for your environment


Last Update:






About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, June 03, 2013 - 2:42:05 PM - Mathias Msemo Back To Top

Thanks Edgewood Solutions ! It worked perfectly beyond my expectation. From this Idea we can automate execution of other SPs as well, not only backup.


Monday, March 25, 2013 - 9:41:01 PM - Ruben Back To Top

 

The stored procedure works great!. I was wondering how we could change it to overwrite the existing backup files. In our case we just want to keep a day work the backup. We are backing up the file to tape every night. Thanks  


Friday, March 08, 2013 - 1:54:39 AM - Manuel Back To Top

Thanks a lot for this perfectly solution! It´s working fine!


Monday, January 28, 2013 - 12:50:09 PM - Mostafa Back To Top

Thank a lot for your help !!!!


Friday, December 07, 2012 - 7:37:29 AM - Raju Pradhan Back To Top

Thanks sir.This is one of the best articles.Its working nicely for me and our clients...


Monday, November 26, 2012 - 3:13:53 PM - Lava Back To Top

follow up to my question: In Windows 2008, you need to put Sqlcmd under program/script, and add -S servername\instancename -i c:\SQbackup\backup.sql next to add arguments(optional).


Monday, November 26, 2012 - 2:59:13 PM - Lava Back To Top

Can you please update your note to show how you setup the scheduled task using windows 2008. I am confused about the action part in 2008. I put under program/script the path to run SQLCMD, under add arguments(optional), I added the sqlcmd -S ... command. This does not seemed to be working, please add a screen shot to show how you do the action part setting the scheduler.

 

Thanks,

Lava

 

 

 

 


Monday, November 05, 2012 - 8:14:48 AM - Vaibhav Back To Top

How to overwrite backup taken by this autobackup utility.


Wednesday, August 01, 2012 - 7:06:10 AM - AGreen Back To Top

Tools such SQLBackupAndFTP in most standart cases really the good choice. We have a customer with no IT staff and SQLBackupAndFTP perfectly solved the daily backup problems, plus the free version let usto to schedule up to 2 database backups daily.


Wednesday, March 28, 2012 - 2:19:13 AM - Aakash Back To Top

Hi,

I have created the Stored procedure in master database ( Programability -> stored procedures ) but still i am getting the below error.

Though, i am able to execute this sql from SQL management studio.

Msg 2812, Level 16, State 62, Server Server1, Line 1

Could not find stored procedure 'sp_BackupDatabase'.


Tuesday, July 05, 2011 - 9:36:45 AM - Alex Green Back To Top

I also recommend using SQL Backup and FTP, the free version lets you backup up to 2 databases daily which makes it an excellent tool for SQL Express.


Tuesday, December 21, 2010 - 8:39:23 PM - Ruslan Back To Top

SQL backup and FTP http://sqlbackupandftp.com is my favorite tool for sql backups


Monday, November 01, 2010 - 11:12:06 AM - Bhavesh Back To Top

In windows xp and w2k3 its working fine with scheduled task 1.0.

but when i am using Windows server 2008 or vista or windows 7 there is Task scheduler 2.0 on this i am not able to set start in path.

can you please help what kind of changes apply on this task scheduler 2.0 ?

 

Thanks

Bhavesh


Wednesday, May 05, 2010 - 9:56:21 AM - killmenot Back To Top

 I also suggest to seea free tool to backup your databases Its name is SQL backup and FTP (http://sqlbackupandftp.com). It's a easy to use. Just try it.


Thursday, October 08, 2009 - 12:13:41 PM - admin Back To Top

What happens when you run this command from a query window?  Does this create a backup file? 

EXEC tzcs_WLaframboise.dbo.sp_BackupDatabase 'tzcs_WLaframboise', 'F'


Thursday, October 08, 2009 - 6:17:26 AM - efficio Back To Top

WHEN I EXECUTE : sqlcmd -S SVR001 -E -i E:\BACKUP_SQLDATABASE\SQLBACKUP.SQL

 

I GET THE ERROR:

Msg 911, Level 16, State 1, Server SVR001, Line 1 Could not locate entry in sysdatabases for database 'tzcs_WLaframboise'. No entry found with that name. Make sure that the name is entered correctly.

HERE IS THE CONTENT OF MY SQLBACKUP.SQL

EXEC tzcs_WLaframboise.dbo.sp_BackupDatabase 'tzcs_WLaframboise', 'F'
GO
QUIT


Tuesday, October 06, 2009 - 4:09:09 AM - admin Back To Top

Did you create this stored procedure in the master database?

If you created this in a user database, lets say called TestDatabase the command in the SQLBACKUP.SQL file should be like this

EXEC TestDatabase.dbo.sp_BackupDatabase 'master', 'F'
GO
EXEC TestDatabase.dbo.sp_BackupDatabase 'model', 'F'
GO
EXEC TestDatabase.dbo.sp_BackupDatabase 'msdb', 'F'
GO
QUIT


Monday, October 05, 2009 - 8:11:53 PM - efficio Back To Top

When I execute the command: sqlcmd -S SVR001 -E -i  E:\BACKUP_SQLDATABASE\SQLBACKUP.SQL

I Get the error: Msg 2812, Level 16, State 62, Server SVR001, Line 1
Could not find stored procedure 'sp_BackupDatabase'.

 However, I can execute the storeprocedure from  management studio by entering the variables and everything works fine.

 

Any ideas?


Tuesday, September 22, 2009 - 11:13:13 AM - admin Back To Top

You need brackets around the database name.  Try this:

 

-- ============================================= 
-- Author: Edgewood Solutions 
-- Create date: 2007-02-07 
-- Description: Backup Database 
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- ============================================= 
CREATE PROCEDURE 
[dbo].[sp_BackupDatabase]  
       
@databaseName sysname@backupType CHAR(1

AS 
BEGIN 
       SET 
NOCOUNT ON


       
DECLARE @sqlCommand NVARCHAR(1000

       
DECLARE @dateTime NVARCHAR(20


       
SELECT @dateTime REPLACE(CONVERT(VARCHARGETDATE(),111),'/',''
) + 
       
REPLACE(CONVERT(VARCHARGETDATE(),108),':',''
)  

       
IF @backupType 
'F' 
               
SET @sqlCommand 'BACKUP DATABASE [' @databaseName 

               
'] TO DISK = ''C:\Backup\' @databaseName '_Full_' @dateTime 
'.BAK''' 
        
       
IF @backupType 
'D' 
               
SET @sqlCommand 'BACKUP DATABASE [' @databaseName 

               
'] TO DISK = ''C:\Backup\' @databaseName '_Diff_' @dateTime 
'.BAK'' WITH DIFFERENTIAL' 
        
       
IF @backupType 
'L' 
               
SET @sqlCommand 'BACKUP LOG [' @databaseName 

               
'] TO DISK = ''C:\Backup\' @databaseName '_Log_' @dateTime 
'.TRN''' 
        
       
EXECUTE sp_executesql 
@sqlCommand 
END


Tuesday, September 22, 2009 - 9:19:21 AM - GANESH100 Back To Top

The script is nice.  However, it is backing up database names with "test" and "test_test".  But, not able to backup a database; if the name is

"test-test".....What could be the reason ?  Thanks.


Friday, July 31, 2009 - 4:27:36 AM - admin Back To Top

If you look at the tip, there is code that creates this stored procedure.

Take a look here:

http://www.mssqltips.com/tip.asp?tip=1174


Friday, July 10, 2009 - 5:39:24 AM - dzinks Back To Top

Where is stored procedure sp_BackupDatabase  ?


Friday, June 19, 2009 - 7:25:17 AM - grobido Back To Top

It depends on what you put ni the command line.

In the backup.sql file you should have the following lines:

sp_BackupDatabase 'master', 'F'
GO
sp_BackupDatabase 'model', 'F'
GO
sp_BackupDatabase 'msdb', 'F'
GO
sp_BackupDatabase 'myOwnDB', 'F'
GO
sp_BackupDatabase 'TestDb', 'F'
GO

QUIT


Thursday, June 18, 2009 - 6:25:17 PM - gtay Back To Top

 Hi, in my MS SQL database I have 2 databases - System Databases, myOwnDB, TestDb. If I were to use your script, will it only backup the system database and not myOwnDB and TestDb?

Thanks!


Monday, March 02, 2009 - 8:38:20 AM - rsteph Back To Top

I found this tip, and have tried to set it up. I actually got the SQL part all set up, and if I simple run the command in the backup.sql file (to call the stored procedure), it makes the backup without problem.

 However, for some reason when I go to place it on in the Scheduled Task list, it doesn't seem to work. Here's the command line I'm using:

"sqlcmd -S dbServer-1 -E -i C:\backup script\Backup.sql"

 Do I need to add the SQL Server name to the Server (i.e. dbServer-1\SQLExpress)? Or does anyone have any other ideas why the schedule might not be working? Like I said, I've tested the stored procedure, so I know that part works; so it has to be in the scheduling part...

 Any help would be greatly appreciated. Thank you.


Monday, October 20, 2008 - 10:07:47 AM - grobido Back To Top

Kendjr, if you look in Configuration Manager you will probably see that the SQL Server service is using the Network Service account.  This account probably has limited access to the folder you are trying to back up to.

You can either change the account in Configuration Manager to use the LocalSystem account

or

you can give write permissions to the Network Service account to the folder where you want to create the backup

or

you can try to save the backup to the default folder which is probably something like this:C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup


Monday, October 20, 2008 - 8:02:11 AM - hollydolly Back To Top

sorry *this* is the page that talks about having to use it:

 

http://devzone.advantagedatabase.com/dz/webhelp/Advantage9.0/server1/sp_backupdatabase.htm


Monday, October 20, 2008 - 8:01:25 AM - hollydolly Back To Top

you'll probably want to see this page, http://devzone.advantagedatabase.com/dz/webhelp/Advantage9.0/server1/sp_restoredatabase.htm

which talks about using sp_RestoreDatabase to use the data.


Thursday, September 25, 2008 - 6:36:09 AM - Zaphoid Back To Top

Thanks

Great Script

Worked first time

Best I have ever found


Friday, August 08, 2008 - 1:06:56 PM - Kendjr Back To Top

Thanks,

 I am not familiar with any of these, what would you recommend doing, and how can I do that?

 Thank you very much for your help.

 Ken


Friday, August 08, 2008 - 12:51:05 PM - grobido Back To Top

This is a permissions problem.  The SQL Server service account probably does not have rights to the folder where you are trying to create the backup files. 

By default the NetworkService account is probalby being used for SQL Express which has limited rights.

You can either grant rights to this account to create files in this folder or you can use a different account for the SQL Server service or write to a folder where this account has permissions to create files.


Friday, August 08, 2008 - 10:24:25 AM - Kendjr Back To Top

I am using VIsta, and I am receiving the following error:

Cannot open backup device 'C:\Users\Ken\Documents\Backup\SQL Goldmine Backups\Princeton3_Full_20080808131951.BAK'. Operating system error 5(Access is denied.).

Can you tell me where I can open up the access?

Thanks,

Ken


Wednesday, July 02, 2008 - 4:14:46 PM - StevenQ Back To Top

 This script also needs brackets eg: [ ] around the database name to support names with characters such as hyphens ( - )


Wednesday, June 25, 2008 - 6:26:40 AM - proof Back To Top

I love this script, but how would Iike to use it to backup another database, that may have users in it at the time.  Is there a way to run this script to an attached database that may be active?


Monday, June 23, 2008 - 6:35:33 AM - grobido Back To Top

You need to fix the quoting around the path.

The line should be like this

       IF @backupType = 'F'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = '''+@backuppath + @databaseName + '_Full_' + @dateTime + '.BAK'''

 

You need to  have three single quotes before the @backuppath variable


Saturday, June 21, 2008 - 5:13:39 PM - prashanth Back To Top

 Hello,

   I am very thankful to the code which i got it from you on taking the backup. I am a .net Programmer.

I have an requirement as to take the backup on different paths (as you have gave "c:\Backup") the path will change any no. of time, so i am planning to send a parameter of path, i tried to run the stored proc as this. but this gives a problem.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 06/22/2008 03:54:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: Edgewood Solutions
-- Create date: 2007-02-07
-- Description: Backup Database
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================
alter PROCEDURE [dbo].[sp_BackupDatabasess] 
       @databaseName sysname, @backupType CHAR(1),
       @backuppath varchar(200)
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @sqlCommand NVARCHAR(1000)
       DECLARE @dateTime NVARCHAR(20)

       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 

       IF @backupType = 'F'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = '+@backuppath + @databaseName + '_Full_' + @dateTime + '.BAK'''
       
       IF @backupType = 'D'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = '+@backuppath + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
       
       IF @backupType = 'L'
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
               ' TO DISK = '+@backuppath + @databaseName + '_Log_' + @dateTime + '.TRN'''
       
       EXECUTE sp_executesql @sqlCommand
END

 

 

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'c:'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

 

I would be very glad, if you can help me. prashanthganathe@gmail.com.

 

Thanks & Regards,

prashanthganathe@gmail.com.

 

 


Tuesday, June 17, 2008 - 6:53:06 PM - grobido Back To Top

This is stored under the Extended Stored Procedures -> System Extended Stored Procedures

Try running this code below to see if this works:

 

use master
go

sp_executesql N'SELECT * FROM sys.databases'


Tuesday, June 17, 2008 - 8:32:31 AM - kangman Back To Top

 just wanted to point out that the stored process sp_executesql is not in SQL Server 2005 Express.  I've looked in the sp folder and nothing is there. 


Learn more about SQL Server tools