Scheduling Backups for SQL Server 2005 Express

SQL Server 2005 Express edition does not have scheduled task and cannot make automatic backups. Here is a simple solution which use windows scheduled tasks, and MS Server SQLCMD file.

1. Create a store procedure to backup a database to folder. File naming is YYYYMMDD_TIME_DatabaseName
which automatically sorts backups by days when you list them in explorer


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[BackupDatabase]
@databaseName sysname,
@folder nvarchar(1024)
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),':','')

SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''' + @folder + @dateTime + '_' + @databaseName + '.BAK'''

EXECUTE sp_executesql @sqlCommand
END

2. Create a sql file which use the store procedure you create in step 1. Save it as you wish, for example c:\backups\backup.sql

BackupDatabase 'master', 'c:\backups\'
GO
BackupDatabase 'ima.bg', 'c:\backups\'
GO
QUIT

3. Use windows scheduled tasks. In wizard step where you have to select program click browse button, and enter
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE

Schedule task every day or as often you want, and before to finish the wizard check "Open advanced properties for this task when i press finish"

4. In Task tab edit Run text box

sqlcmd -S serverName\Instance -E -i c:\backups\backup.sql

That is all story. You just have to update c:\backups\backup.sql if you want to backup more databases