Database backup

When backing up the product-specific databases, make sure you also backup the primary and msdb databases. The database backups should be stored on the backup drive in the Database\dbName folder.

In SQL Server Management Studio, use the Maintenance Plan feature to create a maintenance plan that includes the following subplans.

Recommended settings for all database backup plans

• Set the backup schedule to never expire

• Backup to disk

Recommended settings for full backups

• Include a maintenance cleanup task that follows the back up database task in the full backup subplan to delete all the differential and transaction log backups.

• If you want to keep only one backup on the hard disk, use the Backup databases across one or more files option in the backup task properties and use one task per database so you can give each database its own name. Also, you should select Overwrite for the If backup files exist option.

• If you want to keep multiple backup versions on the hard disk, select the Create a backup file for every database and Create a sub-directory for each database options. This will allow you to make one task for many databases and put the database name and timestamp as part of the file name. You should also include a maintenance cleanup task to cleanup these files.

Recommended settings for differential backups

• Use the Create a backup file for every database and Create a sub-directory for each database options. Make sure you select Differential for the Backup type option.

• Have the maintenance cleanup task in the full backup subplan delete the differential backup files after the full backup is performed.

Recommended settings for transaction log backups

• Use the Create a backup file for every database and Create a sub-directory for each database options. Make sure you select Transaction Log for the Backup type option.

• Have the maintenance cleanup task in the full backup subplan delete the transaction log backup files after the full backup is performed.

• Implement truncation of transaction logs as part of the backup strategy to minimize the amount of storage they use.

Other notes

• The Verify backup integrity setting is optional as it will take additional time to perform.

• Provide an appropriate name for the maintenance plan and the subplans. The following model is recommended: one maintenance plan for all backups, subplans for each scheduled period, and backup tasks for each database.