Back up databases to snapshots in SharePoint Server 2010
Applies to: SharePoint Server 2010, SharePoint Foundation 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
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 (https://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
Verify that the account that is used to back up the databases is a member of the SQL Server db_owner fixed database role.
Open SQL Server Management Studio and connect to the database server.
In Object Explorer, expand Databases.
Select the database that you want to back up, and then click New Query.
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>;