The SQL Server Agent and database maintenance plan wizards are unavailable in SQL Server Express, so a quick and easy way to backup all databases on a schedule is to call a T-SQL script using Windows Task Scheduler. The sqlcmd program can be called from a task to execute the script on SQL Server 2005 or greater and osql can be called on SQL Server 2000. To get to Windows Task Scheduler open the Run dialog box in Windows and enter "control schedtasks".
The following T-SQL script will backup all databases except for tempdb to @BackupLocation (path must already exist and include a trailing slash) on SQL Server 2005 or greater. It does not delete old backups so you may need to include logic for that depending on your situation (perhaps by using a PowerShell or VBScript script with Windows Task Scheduler):
/* Change the @BackupLocation as needed */ DECLARE @BackupLocation AS NVARCHAR(255) SET @BackupLocation = 'c:\dbbackups\' /* Set @CurrentDate to a timestamp in mmddyyyy form */ DECLARE @CurrentDate AS NVARCHAR(255) SET @CurrentDate = REPLICATE('0', 2 - LEN(CAST(MONTH(GETDATE()) AS NVARCHAR))) + CAST(MONTH(GETDATE()) AS NVARCHAR) + REPLICATE('0', 2 - LEN(CAST(DAY(GETDATE()) AS NVARCHAR))) + CAST(DAY(GETDATE()) AS NVARCHAR) + CAST(YEAR(GETDATE()) AS NVARCHAR) DECLARE @DatabaseName AS NVARCHAR(255) /* Get all databases except for the temporary database */ DECLARE Databases CURSOR FOR SELECT name FROM sys.databases WHERE name != 'tempdb' OPEN Databases FETCH NEXT FROM Databases INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Command AS NVARCHAR(MAX) SET @Command = 'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + ' TO DISK = ''' + @BackupLocation + @DatabaseName + @CurrentDate + '.BAK''' EXEC(@Command) FETCH NEXT FROM Databases INTO @DatabaseName END CLOSE Databases DEALLOCATE Databases
If you want to run the script on SQL Server 2000 change "sys.databases" to "master..sysdatabases" since the system views were not included before SQL Server 2005.