You might actually be surprised to find out how
easily you can set up a database snapshot. This simplicity is partly due
to the level at which database snapshots are created: at the database
level and not at the table level. Setting up a database snapshot only
entails running a CREATE DATABASE with the AS SNAPSHOT OF
statement. You cannot create database snapshots from SQL Server
Management Studio or from any other GUI or wizard for that matter. All
must be done using SQL scripts.
Creating a Database Snapshot
One of the first things you must figure out before
you create a database snapshot is whether your source database data
portion has more than one physical file in its allocation. All these
file references must be accounted for in the snapshot. You execute the
system stored procedure sp_helpdb with the source database name as the parameter, as shown here:
EXEC SP_HELPDB AdventureWorks
Go
The detailed file allocations of this database are as follows:
Name FileID File Name
AdventureWorks_Data 1 C:\Server\
MSSQL10.SQL08DE01\MSSQL\DATA\AdventureWorks_Data.mdf
AdventureWorks_Log 2 C:\Server\
MSSQL10.SQL08DE01\MSSQL\DATA\AdventureWorks_Log.ldf
You need to worry about only the data portion of the database for the snapshot:
CREATE DATABASE SNAP_AdventureWorks_6AM
ON
( NAME = AdventureWorks_Data,
FILENAME= 'C:\Server\ MSSQL10.SQL08DE01\MSSQL\DATA\SNAP_AW_data_6AM.snap'
AS SNAPSHOT OF AdventureWorks
go
Creating the database snapshot is really that easy.
Now let’s walk through a simple example showing how to create a series
of four database snapshots against the AdventureWorks source database that represent snapshots six hours apart . Here is the next snapshot to be run at 12:00 p.m.:
CREATE DATABASE SNAP_AdventureWorks_12PM
ON
( NAME = AdventureWorks_Data,
FILENAME= 'C:\Server\ MSSQL10.SQL08DE01\MSSQL\DATA\SNAP_AW_data_12PM.snap')
AS SNAPSHOT OF AdventureWorks
go
These represent snapshots at equal time intervals and can be used for reporting or reverting.
Note
We use a simple naming convention for the database
names for snapshots and for the snapshot files themselves. The database
snapshot name is the word SNAP, followed by the source database
name, followed by a qualifying description of what this snapshot
represents, all separated with underscores. For example, a database
snapshot that represents a 6:00 a.m. snapshot of the AdventureWorks database would have this name:
"SNAP_AdventureWorks_6AM"
The snapshot file-naming convention is similar. The name would start with the word SNAP, followed by the database name that the snapshot is for (AdventureWorks, in our example), followed by the data portion indication (for example, data, data1), a short identification of what this snapshot represents (for example, 6AM), and then the filename extension .snap to distinguish it from .mdf and .ldf files. For example, the snapshot filename for the preceding database snapshot would look like this:
"SNAP_AdventureWorks_data_6AM.snap"
We use the AdventureWorks database for this example. AdventureWorks
currently uses only a single data file allocation for its data portion.
Here’s how you create the first snapshot, to reflect a 6:00 a.m.
snapshot:
1. | Create the snapshot on the source database AdventureWorks:
Use [master]
go
CREATE DATABASE SNAP_AdventureWorks_6AM
ON ( NAME = AdventureWorks_Data, FILENAME= 'C:\Program Files\
Microsoft SQL Server\ MSSQL10.SQL08DE01\MSSQL\DATA\
SNAP_AdventureWorks_data_6AM.snap')
AS SNAPSHOT OF AdventureWorks
Go
|
2. | Look at this newly created snapshot from the SQL Server instance point of view, using a SQL query against the sys.databases system catalog, as follows:
Use [master]
go
SELECT name,
database_id,
source_database_id, — source DB of the snapshot
create_date,
snapshot_isolation_state_desc
FROM sys.databases
Go
This shows the existing source database and the newly created database snapshot:
name database_id source_database_id create_date snapshot_
isolation_state_desc
------------------------------------------------------------------------------
AdventureWorks 7 NULL 2009-02-17 23:37:02.763
OFF
SNAP_AdventureWorks_6AM 9 7 2009-12-05 06:18:36.597
ON
Note that source_database_id for the newly created database
snapshot contains the database ID of the source database. Of course, you
can also look at the database snapshot properties by using SQL Server
Management Studio, as shown in Figure 1.
|
3. | Look at the newly created physical file for the sparse file (for the database snapshot) by querying the sys.master_files system catalog:
SELECT database_id, file_id, name, physical_name
FROM sys.master_files
WHERE Name = 'AdventureWorks_data'
and is_sparse = 1
go
Note that we are focusing on only the sparse files for the newly created database snapshot (that is, the is_sparse = 1 qualification). This query results in the following:
database_id file_id name physical_name
----------- ----------- ------------------------------------------------------
9 1 AdventureWorks_Data C:\Prog...\DATA\
SNAP_AdventureWorks_data_6AM.snap
|
4. | To
see the number of bytes that a snapshot sparse file is burning up, you
can issue a series of SQL statements against system catalog views/tables
by using fn_virtualfilestats and sys.master_files.
However, the following is a quick-and-dirty stored procedure that should
make this task much easier. Just create this stored procedure on your
SQL Server instance (in the master database), and you can use
it to see the sizes of any database snapshot sparse file on your server :
CREATE PROCEDURE SNAP_SIZE_UNLEASHED2008
@DBDATA varchar(255) = NULL
AS
if @DBDATA is not null
BEGIN
SELECT B.name as 'Sparse files for Database Name',
A.DbId, A.FileId, BytesOnDisk FROM fn_virtualfilestats
(NULL, NULL) A,
sys.master_files B
WHERE A.DbID = B.database_id
and A.FileID = B.file_id
and B.is_sparse = 1
and B.name = @DBDATA
END
ELSE
BEGIN
SELECT B.name as 'Sparse files for Database Name',
A.DbId, A.FileId, BytesOnDisk
FROM fn_virtualfilestats (NULL, NULL) A,
sys.master_files B
WHERE A.DbID = B.database_id
and A.FileID = B.file_id
and B.is_sparse = 1
END
Go
When the SNAP_SIZE_UNLEASHED2008 stored procedure is created,
you run it with or without the name of the data portion of the database
for which you have created a snapshot. If you do not supply the data
portion name, you see all sparse files and their sizes on the SQL Server
instance. The following example shows how to execute this stored
procedure to see the sparse file current size for the AdventureWorks_data portion:
EXEC SNAP_SIZE_UNLEASHED2008 'AdventureWorks_Data'
Go
This results in the detail bytes that the sparse file is using on disk:
Sparse files for Database Name DbId FileId BytesOnDisk
------------------------------------------------------------------------------
AdventureWorks_Data 9 1 196608
Currently, the sparse file is very small (196KB) because it was recently
created. Little to no source data pages have changed, so it is
basically empty right now. It will start growing as data is updated in
the source database and data pages are copied to the sparse file (by the
copy-on-write mechanism). You can use the SNAP_SIZE_UNLEASHED2008 stored procedure to keep an eye on the sparse file size.
|
5. | Believe
it or not, the database snapshot is ready for you to use. The following
SQL statement selects rows from this newly created database snapshot
for a typical point-in-time–based query against the CreditCard table:
Use [SNAP_AdventureWorks_6AM]
go
SELECT [CreditCardID]
,[CardType]
,[CardNumber]
,[ExpMonth]
,[ExpYear]
,[ModifiedDate]
FROM [SNAP_AdventureWorks_6AM].[Sales].[CreditCard]
WHERE CreditCardID = 1
go
This statement delivers the correct, point-in-time result rows from the database snapshot:
CreditCardID CardType CardNumber ExpMonth ExpYear
ModifiedDate
--------------------------------------------------------------
-----------------
1 SuperiorCard 33332664695310 1 2010
2009-12-03 00:00:39.560
|
You can take a look at how this all looks from SQL Server Management Studio. Figure 2 shows the database snapshot database SNAP_AdventureWorks_6AM along with the source database AdventureWorks. It also shows the results of the system queries on these database object properties.
You are now in the database snapshot business!
Breaking Down a Database Snapshot
If you want to get rid of a snapshot or overlay a current snapshot with a more up-to-date snapshot, you simply use the DROP DATABASE command and then create it again. The DROP DATABASE
command immediately removes the database snapshot entry and all sparse
file allocations associated with the snapshot. It’s very simple indeed.
The following example drops the database snapshot just created:
Use [master]
go
DROP DATABASE SNAP_AdventureWorks_6AM
go
If you’d like, you can also drop (delete) a database
snapshot from SQL Server Management Studio by right-clicking the
database snapshot entry and choosing the Delete option. However, it’s
best to do everything with scripts so that you can accurately reproduce
the same action over and over.
