You are responsible for managing a SQL Server 2008 instance. You want to create a new database named Salesdb that supports FILESTREAM data.
You execute the following Transact-SQL statement to create the newdatabase:
CREATE DATABASE Salesdb ON
PRIMARY (NAME = Salesdb1,
FILENAME = ‘c:FileDataSalesdbData.mdf’),
FILEGROUP FStreamFileGroup1 CONTAINS FILESTREAM (NAME = Salesdb2,
FILENAME = ‘c:FileDataFstream’)
LOG ON (NAME = SalesdbLog,
FILENAME = ‘c:FileDataSalesdbLog.ldf’);
Which two conditions must be met for this statement to execute successfully? (Choose two.)
A.
The C:FileDataFstream folder must exist.
B.
The C:FileData folder must exist.
C.
The Fstream subfolder in the C:FileData folder must not exist.
D.
The C:FileData folder must not exist.
Explanation:
The C:FileData folder must exist, and the Fstream subfolder in the C:FileData folder must not exist. TheFILESTREAM feature integrates applications that use SQL Server with the NTFS file system. It stores varbinary(max) binary large object (BLOB) data as files on the file system. To be able to use FILESTREAM, you mustenable it on the SQL Server instance that is running the Database Engine. Permissions to the FILESTREAM dataare managed by NTFS, and the SQL Server service is the only service that can access this data.
FILESTREAM provides security to the data. Only the SQL Server service account is granted NTFS permission tothe FILESTREAM container. The FILESTREAM feature uses a special type of filegroup. Therefore, when creatingthe database, you must use the CONTAINS FILESTREAM clause when creating at least one filegroup. The FILENAME
parameter in the FILESTREAM filegroup is used to specify a path. All folders specified in the path upto the last folder must exist. The last folder specified in the path must not exist. For example, in this scenario the C:FileData folder must exist, and the Fstream subfolder in the C:FileData folder must not exist. The option stating that the
C:FileDataFstream folder must exist is incorrect. All folders specified in the path upto the last folder must exist. The last folder specified in the path must not exist. Therefore, in this scenario the Fstream subfolder in the C:FileData folder must not exist. The option stating that the C:FileData folder must not exist is incorrect. All folders specified in the path up to thelast folder must exist. Therefore, the C:FileData folder must exist.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Manage and configure databases.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing FILESTREAM Storage > Designing and Implementing FILESTREAM How-to Topics > How to:Create a FILESTREAM-Enabled Database MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing FILESTREAM Storage > FILESTREAM Overview