639
Chapter 24: Database Snapshots
24
For each data file in the source database, a data file in the database snapshot
exists. The data files in the database snapshot are different as compared to the
source database data files. The data files in the database snapshot are NTFS sparse
files. When you create the database snapshot, the sparse files are empty and do not
contain any user data. The sparse files can potentially grow to the size of the data
file of the source database at the time of the creation of the database snapshot.
Therefore, you must verify that the volume where you want to place the database
snapshot has enough free space.Even though you can create the database snapshot on a volume with little space, ensure that the volume has
enough free space (at least the space of the source database when the database snapshot is created). If the
volume runs out of space, the database snapshot is marked suspect, becomes unusable, and needs to be
dropped.- Execute the CREATE DATABASE Transact-SQL command to create the database
snapshot of the AdventureWorks2012 database:
CREATE DATABASE AdventureWorks2012_Snapshot ON
(NAME = AdventureWorks2012_Data, FILENAME =
'D:\DATABASE SNAPSHOTS\AdventureWorks2012_Snapshot.snap' )
AS SNAPSHOT OF AdventureWorks2012;
GO
The FILENAME appears to wrap in the preceding code. In actual code, it should not wrap around or you get the error
shown here:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "'D:\ DATABASE
SNAPSHOTS\AdventureWorks2012_Snapshot.snap " failed with the
operating system error 123(The filename, directory name, or volume
label syntax is incorrect.).Best Practice
To make it easier to use the database snapshot, think about how you want to name it before you start.
One method is to include the source database name, some indication that it is a snapshot, the time it
was created and a meaningful extension. The previous example uses the name AdventureWorks2012_
Snapshot as the name and .snap as the extension to differentiate the database snapshot fi les from
regular database fi les. The previous example creates the database snapshot on a different volume as
the source database. Placing the database snapshot on a physically separate volume is best practice
because this avoids disk contention and gives better performance.c24.indd 639c24.indd 639 7/31/2012 9:25:48 AM7/31/2012 9:25:48 AM
http://www.it-ebooks.info