If you are looking for the way on how to perform schedule backup of your SQL Server Express 2005 in Windows 7, which has been drastically redesigned and different from , then the guide below may be one of the solutions. SQL Server 2005 Express edition is a free version of SQL Server 2005 which lacks of scheduling backups feature. To overcome this limitation, you may use scheduled task in Windows to perform scheduling backups.

The steps to perform scheduling backups for SQL Server 2005 Express in Windows 7:

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 7 which can be found in Control Panel -> System and Security -> Administrative Tools -> Schedule Tasks.

Click on “Create a Basic Task” and “Create Basic Task” wizard will be displayed. Type in “SQL Express Data Backup” in the “Name” and click “Next” button.

4. Specify when you want the task to be start (Daily), then click “Next” button.

5. Specify what time you want the task to be start, then click “Next” button.

6. Click Start a program to perform the task, then click “Next” button.

7. Click on Browse button to select SQLCMD.exe file from “C:\Program Files\Microsoft SQL Server\90\Tools\Binn” and type the following command in “Add arguments (optional)”: -S serverName -E -i C:\Backup\dbBackup.sql

The meaning of the command:

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

8. Click on “Next” button to finish creating task.

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

Related Posts