Backing up SQL Server data
Published: December 16, 2009
Applies To: Forefront Client Security
To ensure your ability to properly preserve and restore your Client Security environment, you should back up several key databases in SQL Server:
Collection database (or OnePoint database)
Reporting database (or SystemCenterReporting database)
SQL Server reporting database (ReportServer)
Master and MSDB SQL Server system databases
If there are established guidelines and procedures for backing up databases in your organization, then follow those guidelines and procedures to back them up.
|ReportServerTempDB contains temporary data and does not need to be backed up.|
The most important databases to back up are the collection database (or OnePoint database) and the reporting database (or SystemCenterReporting database).
The collection database is the MOM operational database. This database contains data vital to your Client Security deployment, including:
Most Client Security and MOM environment configuration settings
Client Security polices
Client Security and MOM agent information
Management packs with customizations
Operations data that has not been archived to the reporting database
The collection database is constantly being written to and read from, so you must back up the database regularly to preserve the latest information about your Client Security environment. Failure of this database requires the rebuilding of your collection server components and may result in the loss of almost all MOM-specific data. Your backup plans should include the collection database at a minimum.
The reporting database (or SystemCenterReporting database) holds all of the archived operational data collected by MOM and Client Security. This data is used by SQL Server Reporting Services to create and present the reports provided by Client Security. This data is important for trend analysis, performance tracking, and many other IT analysis tasks. Because this database can grow to a very large size over time, backing it up can take longer than it takes for the operational database.
By default, the reporting database is updated daily, so backing up the database more than once each day would not have any additional benefit.
Both the collection and reporting databases use the Simple Recovery model by default. This model disallows the possibility of recovering either database to the point of failure. In the event of failure, you would restore the database to the last full or differential database backup that you created.
To back up these databases, it is recommended that you create a database maintenance plan in SQL Server. A maintenance plan will allow you to schedule backups at regular intervals to suit the needs of your organization. For more information about creating maintenance plans, see Maintenance Plan Wizard in SQL Server 2005 Books Online (http://go.microsoft.com/fwlink/?LinkId=87339).
For more information about backing up MOM databases, see Backup and Restore in the MOM 2005 Operations Guide (http://go.microsoft.com/fwlink/?LinkId=87340).
SQL Server reporting database
The SQL Server reporting database stores report definitions, report metadata, cached reports, and snapshots. This information is updated as administrators manage report definitions. For example, information is updated when an administrator defines new reports or changes the definitions of existing reports.
Master and MSDB databases
The Master database is a system database that records all of the system-level information for a SQL Server system, including the location of the database files. It also records all logon accounts and system configuration settings.
The MSDB database is a SQL Server system database, which is used by the SQL Server Agent to schedule jobs and alerts and for recording operators. This database contains task schedules that are vital to the health of the collection database.
The proper functionality of the Master and MSDB databases is key to the operation of all the databases in a SQL Server instance. You should back up these databases after you first configure Client Security and each time you make a system configuration change or task scheduling change.