MS SQL Server Concepts and Programming Question: Download MS SQL Server PDF

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: emp directory:

USE master
GO

DROP DATABASE GlobalGuideLineDatabase
GO

CREATE DATABASE GlobalGuideLineDatabase
ON (NAME = GlobalGuideLineDatabase,
FILENAME = 'C: empGlobalGuideLineDatabase.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = GlobalGuidelineLog,
FILENAME = 'C: empGlobalGuideLineDatabase.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 GlobalGuideLineDatabase C: empGlobalGuideLineDatabase.mdf 1280
LOG GlobalGuidelineLog C: empGlobalGuideLineDatabase.ldf 128