The following document gives an example of a typical maintenance plan and schedule for the Project Insight database in simple on-premise environment. The document assumes that the reader is a qualified SQL Server database administrator who is familiar with SQL Server, SQL Server Agent and SQL Server Maintenance Plans.
Create Nightly Maintenance Plan (Early AM Daily)
The nightly maintenance plan should include the following maintenance tasks:
1. Backup Database Task
2. Check Database Integrity Task
3. Re-Organize Index Task (Tables and Views)
4. Update Statistics Task
5. Maintenance Cleanup Task (Deletes Backup Files older than X)
Create Weekly Maintenance Plan (Sunday AM)
The weekly maintenance plan should include the following maintenance tasks:
1. Rebuild Index Task (Tables and Views)
Optionally add notification alerts on failure of each of the tasks
above, but most importantly failure of the backup or database integrity
check to succeed.
IMPORTANT: Also, even though a backup of your database file is being created, you MUST also use standard backup software (like Symantec Backup Exec) to backup the database file which is dumped onto a local hard drive by SQL Server. The backup software can be configured to connect via file share or backup agent software to copy the dumped SQL Server backup file into your normal file backup routines.
For more information on creating a maintenance plan to backing up your database, see the following article: