How do you make a SQL Server database snapshot? Have to use T-SQL. Here’s the example from MSDN that will work with the AdventureWorks sample database.

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss’ )
AS SNAPSHOT OF AdventureWorks;
GO

You can’t specify a log, size or growth parameters, since snapshots are read-only.

Once you run this, a Database Snapshot folder appears in SQL Server Management Studio. You can reference the database with the use command.

The database snapshot just stores changes made after the snapshot was taken, so the file stays small relative to the original database file. And following that concept, users querying against the snapshot will still hit the original database, so you don’t relieve utilization there.

Here’s a nice link summarizing more: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1176142,00.html

And what about those other SQL Server snapshots? There is snapshot replication, which is one time read-only replication that is transmitting a physical copy of the data. And there are report snapshots in Reporting Services, which is a cached complete copy of a report.

Advertisements