How to schedule daily backup in MSSQL Server 2008 Web Edition

Question

In MSSQL Management Studio I created a maintenance plan but it won't work

Error is; "Message Executed as user: Administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services. The package execution failed. The step failed."

But in Microsoft page http://www.microsoft.com/sqlserver/2008/en/us/web.aspx in Automate tasks and policies section it is written that backup can be scheduled in this edition but how?

Answer 1

you can automate your backups using the SQL Server Agent, but you cannot use a maintenance plan to do so in Web Edition. Maintenance plans rely upon Integration Services to run, and therefore can't be used in Web Edition. Instead, you will need to write a script using the BACKUP DATABASE command and create a SQL Server Agent job to execute the command on a schedule you decide upon.

This is a standard use of SQL Agent, so you should be able to find lots of examples in Books Online and on the web.

Answer 2

I recently scripted some of our backups at my current job.

DECLARE @backupName varchar(100)
--Set the location for your backup as well as trimming whitespace, :, and - characters
SET @backupName = 'C:\Backups directory\Backup Name_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,CONVERT(date, SYSDATETIME()), 20),'-',''),':',''),' ','') + '.bak'

BACKUP DATABASE [Database Name]
TO DISK = @backupName

This creates a backup at the specified location with a format of DatabaseName_yyyymmdd.bak which is similar to the maintenance tool formatting. All you need to do is apply the script to your databases and then set it to run as a job using the SQL Server Agent.