SharePoint
2010, fully loaded and deployed with Office Web Application, all
Service applications, Logging database, FAST Search, Content Databases,
and Configuration Databases will have approximately 25 databases stored
in SQL Server 2008. That is a huge number of databases to maintain and
certainly a lot of content in these databases! To minimize the loss of
content, it is crucial to incorporate a solid SQL database backup plan
to back up all these databases.
Many options are available
to back up SharePoint databases in SQL Server. In addition to Central
Administration and SharePoint 2010 Management Shell, SQL Server
facilitates the backup and restore of SharePoint 2010 databases with the
SQL Server Management Studio, SQL Server Maintenance Plans, or
Transact-SQL scripts that can be executed from within SQL Server.
Further, a third-party SQL backup engine can also be used to back up
these SharePoint 2010 databases.
Although the backup options
out-of-the-box in SharePoint give an array of tools to back up the
configuration and the content databases; critical SQL Server databases
such as Master, MSDB, and TempDB cannot be backed up by these tools from
within SharePoint. If a catastrophic event or hardware failure occurs,
restoring the full SQL Server installation to the point of failure is
not possible unless there is a strategic plan to do a backup and
recovery of SQL Server databases.
In addition to the standard backup features in SQL Server 2008, Backup Compression has been introduced.
SQL Server Backup Methods
SQL Server offers a wide range of options to back up databases. These include the following:
Full— Complete database backup that includes transaction logs.
Differential— All data changes since the last full backup is backed up.
Transaction log backup— All transactions performed against the database since the last full backup or transaction log back up is backed up.
File and file group backup— A portion of the database is backed up.
Partial backup—
All data in the primary group, every read-write file group, and any
specified files are backed up. File groups marked read-only are skipped.
Differential partial backup—
Although similar to partial backup, this backup records only changes to
the data in the file groups since the previous partial backup.
Copy-only backup—
This backup enables a backup of any type to be taken without affecting
any other backups. Normally a database backup is recorded in the
database and is identified as part of a chain that can be used for
restoration.
To do a full backup of an individual database via the SQL Server Management Studio, perform the following steps:
1. | Open the SQL Server Management Studio (Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio).
|
2. | In the Connect to Server dialog box, select the database server name to connect to, and click Connect.
|
3. | In the left pane of the Object Explorer, expand the server and the database folder.
|
4. | Select the desired SharePoint Database to back up.
|
5. | Right-click the database, select Tasks, and then click Backup.
|
6. | On
the General settings page in the Back Up Database window, review the
name of the database(s) to be backed up and confirm that the Backup Type
option is set to Full.
|
7. | For the Backup component option, select a Database option.
|
8. | In the Backup set, enter the name and the description for the database backup.
|
9. | In
the next Destination section, the Tape option is grayed out if no tape
devices are attached to the database server. In such a scenario, the
only option available is to back up to disk. Click Add to add up to 64
disk devices that will contain the backup information. The same limit
applies to tape media. If multiple devices are specified, the backup
information will be spread across those devices. All the devices must be
present to restore the database.
|
10. | In
the Select Backup Destination dialog box, enter the path and the backup
file name in the destinations on the disk where the database is to be
backed up. Click OK if the backup is to be initiated, or select Options
in the Select a Page pane to configure advanced backup options.
|
In the Overwrite Media
section, you have an array of options to choose from: Append to the
Existing Backup Set or Overwrite All Existing Backup Sets. Backup sets
can be added to an existing media, in which case you select Append to
the Existing Backup Set. If you want to overwrite the backup sets with
the latest backup on the media, you should select the Overwrite All
Existing Backup Sets option.
Note
SQL Server 2008 Enterprise
Edition supports compression. It must be noted that compressed and
uncompressed database backups cannot co-exist on the same media set. It
is to be noted that the Overwriting Backup Sets feature will be removed
from the next version of Microsoft SQL Server; therefore, using this
feature should be avoided in any new development instances, and any
existing applications that use this feature should be modified.
In the Reliability section, you can choose to
Verify Backup When Finished—
Recommended to select because it verifies the database backup, but it
does extend the time it takes to compete the database backup.
Perform Checksum Before Writing to Media—
Ensures that the database backup is completed without any errors.
However, selecting this option adds to the time it takes to complete the
database backup.
Continue on Error—
Database backup continues even if an error has been encountered.
Selecting this option has an impact on the performance of the server,
because it increases the CPU overheads.
The next Transaction Log
section is available only if Backup type selected on the General page is
of the type Transaction Log. The Truncate the Transaction Log option
removes any inactive portion of the transaction log after the database
backup is complete. This is the default option and helps keep the size
of your transaction log manageable.
The set of options in the
Tape Drive section is enabled only when Tape has been selected for the
destination media. The Unload the Tape After Backup option ejects the
media tape after the backup completes. This can help identify the end of
the backup and prevents the tape from being overwritten the next time
the backup runs. The Rewind the Tape Before Unloading option causes the
tape to be released and rewound prior to unloading the tape.
New in SQL Server 2008 is the
backup compression feature. This is the last option in the backup
database option section. This feature is a Microsoft proprietary formula
that may shrink the size of the database backup down to 20 percent of
the original size. This is dependent on the value specified in the
backup-compression default server configuration option.
Caution
Using the compression option can increase the load on the CPU and affect the processing power.
Continue with the process as follows:
1. | In
the Options page and Overwrite Media section, maintain the default
settings Back Up to the Existing Media Set and Append to the Existing
Backup Set.
|
2. | In
the Reliability section, choose Verify Backup When Finished, Perform
Checksum Before Writing Media, and Continue On Error Options. Click OK
to execute the backup.
|
3. | Review the success or failure error messages and click OK to finalize.
|
4. | Repeat for additional SharePoint databases.
|
Understanding the SQL Server Recover Models
There are three recovery
models to choose from: Simple, Full, and Bulk-Logged. The choice of the
model depends on various factors that need to be considered, such as the
extent to which data loss is acceptable, performance of the SQL Server,
and database recovery to the point of failure.
The simple recovery model
recovers the database only to the point of the last successful full or
differential backup. Content added to the database after the backup
cannot be recovered with this model.
The full recovery model
recovers the entire database to any point in time, because transaction
logs are maintained. It must be noted that because all transactions to
the database are logged, SQL Server database performance tends to
degrade. It is recommended that transaction logs and the database files
are stored on separate hard disks for performance enhancement and
recovery.
The bulk logged recovery model
is similar to the full recovery model because it maintains a
transaction log; however, this model should be used only in situations
where large amounts of data are written to the database. To improve
performance of the database server in such situations of bulk insertion
or indexing, the recovery model should be switched to the bulk logged
model temporarily.
Business requirements define
the disaster recovery strategies and drive the database administrator’s
decision on choosing the appropriate recovery model for the database. By
default, the SharePoint configuration, AdminContent, and site content
databases’ recovery model is set to Full. As a result, these databases
can be restored to the point of failure.
To set the recovery model on a SharePoint content database, perform the following steps:
1. | Open the SQL Server Management Studio (Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio).
|
2. | On the screen, select the database server to connect to the SQL database.
|
3. | In the left pane of the Object Explorer, expand the server and then the database folder.
|
4. | Select the desired SharePoint Database to back up. Right-click on the database, and select Properties.
|
5. | In the Database properties dialog box, select the Options node.
|
6. | In
the Recovery Mode dialog box, select Full, Bulk-Logged, or Simple from
the drop-down list. Full is typically selected in most cases. Click OK
to save the changes.
|
