This can be a real time-saver! Do you know of the concept of Snapshots in SQL Server 2005? No? It is one of the new features available in the Enterprise edition of SQL 2005.
Database Snapshots allows you to create a read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data. Currently, you can do that by running a backup and restoring it to another database. However, the big advantage you gain by using Database Snapshots instead is the speed at which a snapshot occurs, as well as the ability to create multiple snapshots at different points in time quickly.
If you want to read more about the concept, the advantages and the disadvantages you can read the great post of Greg Robidoux at:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1176142,00.html#cons
For SharePoint development it is a great way to test some configuration, and if you changed your mind, roll back all the changes you've made in just a few seconds! This can be handy for testing scripts, for manual configuration in the UI, or for example to give demo's.
The following scripts create and restore a snapshot on a SharePoint Content Database (please see the limitions at the bottom of this post)
Creating the snapshot
To create the snapshot you can run the following T-SQL (run a new Query dialog in SQL Server Management Studio or create a .sql file)
CREATE DATABASE WSS_Content_Snapshot ON -- [The name of the snapshot]
(
NAME = WSS_Content_Snapshot, -- [The name of the logical filename]
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Snapshots\WSS_Content_Snapshot.ss' -- [The filename and location of the snapshot]
)
AS SNAPSHOT OF WSS_Content -- [The original Content Database]
Restoring
Restoring a database is as easy as the following line:
RESTORE DATABASE WSS_Content FROM DATABASE_SNAPSHOT = 'WSS_Content_Snapshot'
You might run into an error if other users (SharePoint processes) are still connected to the database:
Database state cannot be changed while other users are using the database 'WSS_Content'
RESTORE DATABASE is terminating abnormally.
To avoid this you can first run a script to kill all existing processes:
-- First, kill all active processes
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'WSS_Content'
DECLARE @SQL varchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
EXEC(@SQL)
-- Second, Restore the database from the snapshot
RESTORE DATABASE WSS_Content FROM DATABASE_SNAPSHOT = 'WSS_Content_Snapshot'
Limitions
Please note that the snapshot above is limited to the Content Database, the changes you can restore include:
- Adding/Deleting/Editing Subsites
- Adding/Deleting/Editing Lists
- Adding/Deleting/Editing List-items
- Adding/Deleting/Editing Content Types
- Adding/Deleting/Editing Webparts
- Enabling/Disabling Features
- Adding/Deleting/Editing Workflows
- Editing the Navigation
- Editing the Sitecollection in the Site Settings (not deleting or creating sitecollections!)
- ...
Some things you cannot do:
- Sitecollections are registered in the configuration database, if you add or delete sitecollections you must ensure you also snapshot the configuration database
- Solution packages (wsp files) are registered at the Farm level, if you add or delete solution packages you must ensure you also snapshot the configuration database
This is not an exhausting list, please beware of any configuration done at the Farm level since this is registered in the configuration database. If you really want to snapshot your whole environment I suggest you lock all databases, create snapshots of all databases and restore all databases at the same time. You might want to try some variations, but I would not recommend using it in any environment containing critical data since it is not supported my MS.