SQL Server FAQ - Creating Databases with Specified Physical Files

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How to create database with physical files specified?

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

(Continued on next topic...)