Microsoft® SQL Server® 2012 Bible

640

Part V: Enterprise Data Management

After you create the database snapshot, you can view it using Management Studio. Using

Object Explorer in Management Studio, connect to the SQL Server instance. Expand

Databases and then Database Snapshots to see all the database snapshots, as shown in

Figure 24-3.

FIGURE 24-3

Viewing database snapshots in Object Explorer.

Query the sys.databases catalog view, and review the source_database_id column. If this column is Null,

then it is a regular database; if it is Non-Null, it represents the source database ID for the database snapshot.

To find out the space used by the database snapshot, open Windows Explorer, right-click

the data file of the database snapshot, and select properties, as shown in Figure 24-4. The

Size value (183MB in Figure 24-4) is not the actual size of the file. It is the maximum size

of the file and should be approximately the same size of the source database when the

database snapshot was created. The Size on Disk value 2.81MB (2,949,120 bytes), as shown

in Figure 24-4, is the actual size of the database snapshot data file.

Alternatively, you can also find the size using the dynamic management view sys.dm_io_

virtual_file_stats as shown here:

SELECT size_on_disk_bytes FROM

sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012_Snapshot'),

1);

GO

Results:

2949120

c24.indd 640c24.indd 640 7/31/2012 9:25:49 AM7/31/2012 9:25:49 AM

http://www.it-ebooks.info