You have been hired as the database administrator of your company. You are responsible for managing aninstance of SQL Server 2008 that contains a database named
Corpdb . There are several database snapshots created for the Corpdb database. You want to identify the names of thesparse files that are created for the Corpdb
database. Which Transact-SQL statement should you execute?

A.
SELECT name FROM sys.database_files
WHERE database_id = DB_ID(N’Corpdb’);

B.
SELECT physical_name FROM sys.database_files
WHERE database_id = DB_ID(N’Corpdb’);

C.
SELECT name FROM sys.master_files
WHERE database_id = DB_ID(N’Corpdb’);

D.
SELECT physical_name FROM sys.master_files
WHERE database_id = DB_ID(N’Corpdb’);

Explanation:

You should execute the following Transact-SQL statement:
SELECT physical_name FROM sys.master_files
WHERE database_id = DB_ID(N’Corpdb’);
Sparse files are used by database snapshots to store data. Sparse files are created with the file names that arespecified in the
CREATE DATABASE
statement when creating the database snapshot. These file names arestored in the physical_name column of the sys.master_files catalog view. To query the physical_name
columnof the sys.master_files catalog view for a particular database, you should run the following Transact-SQLstatement:
SELECT physical_name FROM sys.master_files
WHERE database_id = DB_ID(N’ <name_of_the_database> ‘);
The sparse files are automatically deleted when you drop the database snapshot. To delete a database snapshot,you would require the
DROP DATABASE
permission. You should not run the following Transact-SQL statement:
SELECT name FROM sys.database_files
WHERE database_id = DB_ID(N’Corpdb’);
The sys.database_files view is a per-database view that contains information about each file of a database asstored in the database itself. The sparse file names of a database snapshot are stored in the physical_name column of the sys.master_files catalog view. Therefore, you should query the sys.master_files view. Also, the name column contains the logical name of the file in the database. It does not display the operating-system filename. You should not run the following Transact-SQL statement:
SELECT physical_name FROM sys.database_files
WHERE database_id = DB_ID(N’Corpdb’);
The physical_name column in the sys.database_files view always contains the file names of the sourcedatabase files. You should not run the following Transact-SQL statement:
SELECT name FROM sys.master_files
WHERE database_id = DB_ID(N’Corpdb’);
The name column contains the logical name of the file in the database. It does not display the operating-systemfile name.

Objective:
Maintaining a SQL Server Database

Sub-Objective:
Manage database snapshots.

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Database Snapshots > UnderstandingSparse File Sizes in Database Snapshots