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.
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.
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.
Click on the “Open advanced properties” to edit the command.
Type the following command in Run:
sqlcmd -S prog3 -E -Q "EXECUTE sp_BackupDatabase 'master', 'F'"
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”.
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.
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.
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.
Click on the “Open advanced properties” to edit the command.
Type the following command in Run:
sqlcmd -S prog3 -E -Q "EXECUTE sp_BackupDatabase 'master', 'F'"
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”.
