MSSQL%20Interview%20Questions%20and%20Answers

Question: How to create database with physical files specified in MS SQL Server?

Answer: If you don't like the default behavior of the CREATE DATABASE statement, you can specify the physical database files with a longer statement:

CREATE DATABASE database_name
ON (NAME = logical_data_name,
FILENAME = physical_data_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
LOG ON (NAME = logical_log_name,
FILENAME = physical_log_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)

For example, the following statement will create a database with database files located in the C:temp directory:

USE master
GO

DROP DATABASE withoutbookdb
GO

CREATE DATABASE withoutbookdb
ON (NAME = withoutbookdb,
FILENAME = 'C:tempwithoutbookdb.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = GlobalGuidelineLog,
FILENAME = 'C:tempwithoutbookdb.ldf',
SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)
GO

SELECT type_desc, name, physical_name, size
FROM sys.database_files
GO
type_desc name physical_name size
ROWS withoutbookdb C:tempwithoutbookdb.mdf 1280
LOG GlobalGuidelineLog C:tempwithoutbookdb.ldf 128