SQLTeam.com | Weblogs | Forums

Schedule backup


I need to schedule daily backup at 6:00am. I don't clearly know how to do this. Can you help me? Thanks in advance


The easiest perhaps is to use the Maintenance Plan Wizard. Launch the wizard from the SSMS object explorer (see screenshot below) and it will walk you through the steps including schedules.


How about Log Backups?

If the data in your database is mission critical, and in particular if any changes cannot easily be re-created (e.g. users type into the database direct, e.g. from Phone discussions etc. rather than key-to-disk from incoming postal purchase invoices which could easily be repeated) then Transaction Logging & Backups should really be enabled. If you then backup the Transaction Log every, say, 10 minutes then your maximum data loss is 10 minutes (assuming that the backup is not lost as well as the database / server etc ...)

This is handled by the Recovery Model setting on your database.

SIMPLE Recovery Model means that any transaction which is logged is then "purged" as soon as the transaction is completed. There is no need to take a Transaction log backup (indeed, the system will not allow you to).

FULL Recovery Model means that SQL will store all transactions UNTIL the Transaction Log has been backed up, and only then will it purge them (technically it doesn't purge them, it just flags them to allow that disk space to be reused). If you use this model it is critical that you take a Transaction Log backup regularly - otherwise the Transaction Log file will grow continuously - until the disk is full!! If you use this model my advice is not to backup once a day, or once an hour, as there is nothing to stop you backing up every 10 minutes, or even more often, as this governs you "Maximum data loss" window interval. There is small amount of housekeeping for each backup, but that apart the total backup disk requirement is the same whether done once a day or every couple of minutes.


Check out Minion Backup. (google is your friend)