Export (0) Print
Expand All

Back up databases to snapshots in SharePoint Server 2010

Published: May 12, 2010

This topic describes how to back up a farm database to a snapshot.

You can only use SQL Server tools to back up a farm database to a snapshot.

Important Important:

You must be running Microsoft SQL Server 2008 with Service Pack 1 (SP1) and Cumulative Update 2 Enterprise Edition to take database snapshots.

We recommend that you regularly back up the complete farm. Regularly backing up the farm reduces data losses that might occur from hardware failures, power outages, or other problems. It is a simple process and helps to ensure that that all the farm data and configurations are available for recovery, if that is required. For more information, see Back up a farm in SharePoint Server 2010. However, IT requirements might require that you backup databases to snapshots. Although you can back up any farm database to a snapshot, you typically back up content databases.

A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected). For more information about database snapshots, see Database Snapshots (http://go.microsoft.com/fwlink/p/?LinkId=163950).

Task requirements for backing up databases to snapshots in SharePoint

Before you begin, you must create a folder on the database server. If you want to store the snapshots at another location, you can move the backup files to a backup folder on the network after the operation is finished.

Use SQL Server tools to back up a database to a snapshot in SharePoint

If you want to back up databases to snapshots, you must use SQL Server tools. The databases that are associated with the farm are determined by the service applications and features that you have installed on the farm.

To back up a database to a snapshot by using SQL Server tools

  1. Verify that the account that is used to back up the databases is a member of the SQL Server db_owner fixed database role.

  2. Open SQL Server Management Studio and connect to the database server.

  3. In Object Explorer, expand Databases.

  4. Select the database that you want to back up, and then click New Query.

  5. Copy the following text, and then paste it to the query pane.

    CREATE DATABASE <snapshot name>
    ON
    (
    NAME=<logical name of the database file>,
    FILENAME = 'c:\WSS_Backup1.ss')
    AS SNAPSHOT OF <database name>;
    
    

Other Resources

Database Snapshots

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft