I have been recently visited client who uses SQL Server 2005 Express Edition for his business. As you know that edition is supplied without SQL Server Agent (which was in MSDE). So,the question is how to schedule backup of the database in that case? I would like to thank to Jasper Smith, who wrote these greate articles
Automating Database maintenance in SQL 2005 Express Edition Part I
Automating Database maintenance in SQL 2005 Express Edition Part II
CREATE PROCEDURE usp_BackupDatabases
DECLARE @dt VARCHAR(20) ---date of db backup
DECLARE @FileName VARCHAR(50) ---build path and dbname
DECLARE @sql VARCHAR(100) ---build final sql statement
SET @dt =CONVERT(VARCHAR(15),GETDATE(),112)
SELECT @FileName = @backup_path+@dbname + @dt+'.bak'
SET @sql = N'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @FileName + ''''
The above is simple stored procedure that accepts two parameters and performs BACKUP of the database.Now that, we have created stored procedure lets put the call of the sp into a text file ( I use userdbfullbackup.sql) and place the file on filesystem.
SQLCMD utility 'knows' to accept parameters (-v),so we can easily provide parameters.
EXEC master..xp_cmdshell 'SQLCMD -S URID\SQLSERVERDEV2005 -i"c:\userdbfullbackup.sql" -v DB="Northwind" -v BACKUPFOLDER="c:\"'
But wait , till now we just written a stored procedure that backups our databases and tested it , so what is about scheduling?
Windows Servers/XP have Scheduled Tasks Wizard
You can refernce to below KB
Please see Jasper's explanation ( do not want repetition).