The purpose of creating SQL Server backups is to minimize the loss of data in a damaged database. In accordance with the Microsoft recommendations (MS191239 SQL.90), we recommend the following:
- All databases should be setup using the Full Recovery model (Please see Full Recovery Model).
- A database maintenance plan should be created and scheduled to run nightly after the close of business or during the least amount of database activity (See Microsoft Library. The maintenance plan should include a Full database backup, check database integrity, shrink database, rebuild index, update statistics, clean up history and maintenance cleanup task. Ideally, this backup will be saved onto a different computer via a mapped drive, onto tape or some other media.
- Another database maintenance plan should be created and scheduled to run daily during the middle of your business day. This plan should include a differential backup, clean up history and maintenance cleanup task. Ideally, this backup will be saved onto a different computer via a mapped drive, onto tape or some other media.
- Finally, another database maintenance plan should be created to run frequently during the business day. The frequency should be based on the average number of transactions per day. The higher the volume, the more frequently it should be scheduled. Note that this plan only needs to occur during the business day and at times that the previous two plans are not running. This plan should include a transaction log backup, clean up history and maintenance cleanup task. Ideally, this backup will be saved onto a different computer via a mapped drive, onto tape or some other media.