SQL 2005 Express and SQL 2000 MSDE have a few differences between them, but one major difference is that unlike MSDE, SQL Express no longer has the SQL Agent.  The SQL Agent is used for scheduling tasks such as stored procedures, data exports, but more importantly, Backups.

Having implemented quite a few SQL Express installs, it became necessary to find ways of scheduling nightly backup jobs.  While searching the web, I stumbled across a great "tips n tricks" site that provided insight that you can use SQL Commands and the Windows Scheduled Tasks utility.

The first step is to create a stored procedure on the Master database that accepts some parameters as to what database to backup and if you want full, differential, or the logs.  The procedure is below

USE [master] 
GO 
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 02/07/2007 11:40: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
-- ============================================= 
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

 

Once that stored procedure is in place, you can write a SQL file calling the procedure with each database name you wish you backup. 

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

Save the file as backup.sql

Lastly, using the Windows Scheduled Tasks utility, you can schedule the backup to run whenever you wish.  First you'll need to locate the SQLCMD.EXE inside the Binn folder of your SQL Express install.  Next you'll need to call the SQLCMD.EXE with the correct parameters. 

• The parameters are as follows
  • -S (the server name\instance)
  • -E (this allows for trusted connections)
  • -i  (this specifies the input file that was created above)

An example of the full command line is: SQLCMD.EXE -S DBServer -E -i C:\Scripts\Backup.sql

Once the wizard steps are finished, your scheduled backups should be functional. 

As mentioned above, this is just a quick overview of a great post that I found.  For the full post in its in-depth, and step-by-step awesomeness check out :  http://www.mssqltips.com/tip.asp?tip=1174