I have scripted out the backup as you can see below however I cannot figure out how to fire it off automatically. Clearly we would like to have this run in the evening after hours. When I use the 'Task Scheduler' it forces a login and then doesn't automatically run. Is this not possible to automate like the other SQL versions? Or do I need to write something completely different?
BACKUPDATABASE [ChickenOut] TO DISK = N'd:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008EXPRESS\MSSQL\Backup\ChickenOut.bak' WITH NOFORMAT, INIT, NAME = N'ChickenOut-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
declare@backupSetId as int
select@backupSetId = position from msdb..backupset where database_name=N'ChickenOut' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ChickenOut' )
if@backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''ChickenOut'' not found.', 16, 1) end
RESTOREVERIFYONLY FROM DISK = N'd:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008EXPRESS\MSSQL\Backup\ChickenOut.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
Here is a thread which hopefully covers off the topic:
There's no need to invent a bicycle here, it is much more convenient to use an off-the-shelf software designed to do exactly that. SQLBackupAndFTP for example would do all that you need and is a freeware for 2 scheduled database backups. DBMaint was quite popular too, but is not a freeware any more.
- Proposed as answer by Mike Shilov Thursday, May 07, 2009 7:12 AM
Here's another option: http://www.sqldbatips.com/showarticle.asp?ID=29
(DbMaint is no more, I'm afraid. I was one of the developers behind it, and we haven't done any work on the tool since approx 2001. Too small market.).
I wouldn't be surprise if ola.hallengren.com 's procedures also can work well with Express.
Tibor Karaszi, SQL Server MVP | web | blog