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

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 FyiCenterData
GO

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

⇒ Renaming Database Names in SQL Server

⇐ Location of Database Files in SQL Server

⇑ Managing Databases and Physical Files in SQL Server

⇑⇑ SQL Server Database Tutorials