Backing up SQL Server, including Reporting Services
The Backup and Restore Wizard in Windows Server 2008 uses Volume Shadow Copy Services (VSS) to back up Microsoft SQL Server databases. An alternative solution that can be used while SQL Server runs is the built-in backup. Use Microsoft SQL Server Management Studio to create a backup of the SQL Server databases. Then, you can run a backup job from the Backup and Restore Wizard to include database backups that Reporting Services created. You would schedule the backup routing in Reporting Services to run first, followed by a backup job run in the Backup and Restore Wizard. For more information about SQL Server backups, see SQL Server Books Online.
Microsoft Dynamics CRM creates at least two Microsoft Dynamics CRM-specific databases on SQL Server. In addition, Microsoft Dynamics CRM requires the default master and msdb SQL Server databases for database services and the default report server SQL Server databases for Reporting Services. The databases that make up a Microsoft Dynamics CRM system on SQL Server are as follows:
OrganizationName_MSCRM
MSCRM_CONFIG
ReportServer
ReportServertempdb
master
msdb
Note
Your Microsoft Dynamics CRM deployment may include more than one OrganizationName_MSCRM database.
The SQL Server backup plan should address each of these databases to make sure that Microsoft Dynamics CRM could recover if one, or all, databases fail. If your organization already has SQL Server or another database application, your database administrator may have a database backup strategy. However, if this is the first database application in your organization, you can create and maintain scheduled jobs to perform the necessary backups by using the Maintenance Plan Wizard in Microsoft SQL Server Management Studio. To start the Maintenance Plan Wizard, in Reporting Services, expand the server, expand the Management folder, right-click the Maintenance Plans folder, and then click Maintenance Plan Wizard.
Your backup plan for the Microsoft Dynamics CRM databases provides you a backup set that includes a full database backup and some number of transaction log backups, depending on the Microsoft Dynamics CRM installation and the frequency with which you determine whether you must have backups. For more information about backup and restore strategies, see SQL Server Books Online.
For databases that are updated infrequently, such as the msdb database, you might perform only full database backups. The OrganizationName_MSCRM, MSCRM_CONFIG, and ReportServer databases should have both full database and transaction-log backups.
Databases on which transaction log backups will be performed must have the Full recovery model database property set. You can set this property through Microsoft SQL Server Management Studio. For more information about how to set database properties, see SQL Server Books Online.
Schedule full database backups frequently enough to reduce the number of restores after a failure. For example, if one day's data loss is acceptable, you can back up the transaction log one time per day, and back up the database one time per week. If only one hour's maximum data loss is acceptable, you can back up the transaction log one time per hour. To reduce the number of restores, back up the database one time per day.
To create a database maintenance plan for scheduled backups, run the Maintenance Plan Wizard from Microsoft SQL Server Management Studio. Select the option to back up the database as part of the maintenance plan for a full database backup. Select the option to back up the transaction log as part of the maintenance plan for a transaction log backup.
Your computer that is running SQL Server should also be designed with a level of fault-tolerance that is correct for a database server. This includes a RAID-5 disk array for your databases and a RAID-1 (mirror) for your transaction logs. With the correct level of hardware fault-tolerance, restoring from backup should be a very uncommon occurrence.
For information about the other options available in these maintenance plans, such as where to store the backups, see the Maintenance Plan Wizard topics in SQL Server Books Online.
For more information about how to back up and restore SQL Server databases, see: