How To Perform Scheduled Backups For SQL Server 2005 Express

SQL Server 2005 Express edition is a free, lightweight and embeddable version of SQL Server 2005 which includes SQL Server Management Studio Express for users to easily manage that databases. Although SQL Server 2005 Express edition supports backup and restore database but it does not supports scheduling backups.

Below are the simple steps to perform in order to enable scheduling backups for SQL Server 2005 Express:

1. Create a store procedure that allows generate the dynamic backup file name, with types of backup to run such as full, differential or transaction log backups and location of the Backup files.

   USE [master]
   
   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
   

2. Create a SQL script to run the backup. In this example, we will backup database master and saved the below SQL script as dbbackup.sql and save in “c:\Backup” folder.

   sp_BackupDatabase 'master', 'F'
GO
QUIT

3. Create a scheduled task in Windows which can be found in Control Panel or Accessories -> System Tools -> Scheduled Tasks or Task Scheduler.

   

4. Click on Add Scheduled Task or Create Task. Scheduling wizard will be displayed. Click Next, then click the Browse button to find SQLCMD.EXE from “C:\Program Files\Microsoft SQL Server\90\Tools\Binn”.

   

   In Task Scheduler, define the above in Action tab.

5. Specify when to perform the task as well as the user name and password to run the operation. Once finished, give the scheduled task a name and save the task.
6. Click on the “Open advanced properties” to edit the command.

   

   Type the following command in Run:

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

   The meaning of the command:

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

If you want to test the task which has been created then you can go back to the Scheduled Tasks or Task Scheduler, right click on the task and select “Run”.

Related Articles

• Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections
• Uninstall and Remove Multiple Database Instances of Microsoft SQL Server 2005
• How to Set Compatibility Level for SQL Server 2008 or 2005 Database
• Microsoft Virtual Server 2005 R2 SP1 (Enterprise Edition) Direct Download Links
• Internet Explorer (IE) Hang When Opening Virtual Server 2005 Web Administration Site
• Microsoft Virtual Server 2005 R2 Download Available for Free
• Microsoft DreamSpack Offers Free Download and License of Visual Studio 2008, Server 2003, SQL Server 2005 and More
• Restore Windows XP and 2003 .bkf Backups in Windows Vista and Longhorn Server with NT Backup Restore Utility
• Optimize SQL Server 2000, 2005 or 2008 in Large RAM System by Locking Pages in Memory and AWE
• Download Paragon Drive Backup 9.0 Free Edition (Express)

This entry was posted on Wednesday, August 26th, 2009 at 1:33 am and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.