Backing up SharePoint-related SQL Server databases

Find out exactly which database backup and recovery models you'll need for a full backup and restore of a SharePoint-associated SQL Server database.

SharePoint administrators often make good use of the included tools when backing up and restoring SharePoint-related...

SQL Server databases. However, these tools cannot be used to restore a full SQL Server installation to the point of failure. To overcome this obstacle, SharePoint administrators should be aware of the following SQL Server elements:

  • SQL Server systems databases
  • The SQL Server databases backups available
  • SQL Server recovery models

Knowledge of these three elements -- which are part of the SQL Server utility package -- will give SharePoint admins the information they need to back up and recover a full SQL Server installation, including all of its SharePoint and system databases.

SQL Server system databases

Backing up SQL Server system databases ensures that a SQL Server instance can be recovered in the event of failure. To understand the importance of backing up, consider the function of the following system databases:

  • SQL Server master database -- This database maintains system level information pertaining to the SQL Server instance. Be sure to back up this database regularly because SQL Server will not function if the master database is damaged.

  • SQL server model database -- This database acts as a template for all databases created on the SQL Server instance. You have to back up the model database if your organization needs to restore the template to maintain customized settings for new databases.

  • msdb -- All of the SQL Server alerts, jobs, operators and backup history tables associated with the SQL Server agent are included in this database. Make sure you back it up every time an item is changed.

  • tempdb -- This database is a temporary workspace for holding intermediate result sets. It is recreated each time a SQL Server instance starts, which works out well because it is impossible to back up.

Types of database backups available

The backup utility included in SQL Server has several options for backing up databases, which gives SharePoint admins the opportunity to select a backup that best suits their needs. The backup options include:

  • Full backup -- Backs up the full database, including all file groups and transaction logs.

  • Differential backup -- Backs up all of the modified pages in a database after the last successful full backup is completed.

  • Transaction log backup -- Backs up all of the transactions performed against the database after the last successful full backup or transaction log backup is completed.

  • File and file group backup -- Backs up a portion of the database at a time.

  • Partial backup -- Backs up all of the data in the primary filegroup, every read-write filegroup and any optionally-specified files. All filegroups marked as read-only are skipped to save time and space.

  • Differential partial backup -- This backup only records data that has changed in the filegroups since the preceding partial backup.

  • Copy-only backups -- This allows a backup of any type to be taken without affecting other backups. Normally, a database backup is recorded in the database itself and is identified as part of a chain that can be used for restoration.

SQL Server recovery models

Three recovery models are associated with a SQL Server database: simple, full and bulk logged. Each model addresses different scenarios on performance, minimization of data loss and recovery of a database to the point of failure.

  • The simple recovery model truncates the transaction log. As a result, a database can only be recovered up until the last successful full or differential database backup. Data that is entered into the database after a successful full or differential database backup is lost.

  • The full recovery model maintains the transaction logs, which makes it possible to restore a database to the point of failure. Database files and transaction logs should be stored on separate hard disks or RAID sets for performance and recovery. Maintaining a transaction log degrades SQL Server performance as all transactions to the database are logged.

  • The bulk logged recovery model maintains a transaction log; however, transaction logging is automatically turned off in order to maximize database performance when large amounts of data are inserted into the database.

The best way to choose a recovery model is to identify how much data an organization is prepared to lose. SharePoint configuration, AdminContent and site content databases' recovery models are set to Full by default. As a result, they can be restored to the point of failure.

The backup utilities included in SharePoint provide a simple way to back up SQL Server databases associated with Microsoft SharePoint. However, backing up with the SQL Server utilities provides additional advantages and flexibility such as full and differential backups that can be configured for very large databases. Additionally, you can leverage existing SQL Server backup and recovery strategies so that the whole SQL Server instance is not only protected but can also be restored in the event of a failure.

Ross Mistry is a principal consultant at Convergent Computing, bestselling author and SQL Server MVP. He focuses on implementing SQL Server, Active Directory and Exchange solutions for fortune 500 organizations residing in the Silicon Valley. Ross' SQL Server specialties include high availability, security, migrations and virtualization. His recent books include SQL Server 2008 Management and Administration and Windows Server 2008 Unleashed. Ross frequently writes for techtarget.com and speaks at international technology conferences around the world. His blog can be found at networkworld.com.

Shirmattie Seenarine is an independent technical writer with more than 10 years of experience. She has contributed to many books, including and Windows Server 2008 Unleashed, Exchange Server 2007 Unleashed, SharePoint Server 2007 Unleashed, and SQL Server 2008 Management and Administration. Shirmattie has written many articles, whitepapers, design documents and operational procedures for Fortune 500 organizations including Microsoft, CIBC, Solectron, Network Appliance and Gilead Sciences.

Dig Deeper on SharePoint administration and troubleshooting