MS SQL Server 2012 - DBA Articles

CREATE DATABASE - Multi Disks Data and Log Files that deployed with Filegroups



Example 03

Multi Disks Data and Log Files that deployed with Filegroup

The following example is used to create DW_Test_Database in the SQL Server. When execute this T-SQL statement, SQL Server should create user defined DW_Data.mdf, DW_Data_Mart_X_Data.ndf, DW_Data_Mart_Y_Data.ndf, DW_Data_Mart_Z_Data.ndf and DW_Log.ldf files along with specified file size information in the form of MB suffix.

DW_Test_Database is a simple data warehouse database that estimation data volume is 100 GB. This database is defragmented into four different 25 GB data files that located in four different disks. These defragmented data files are combined through Filegroup. Filegroup is used to optimize physical storage structure of database.

-- Example 03 --

   

USE master;

GO

   

CREATE DATABASE DW_Test_Database

ON PRIMARY

( NAME = DW_Data,

  FILENAME = 'C:\DW_Test_Database\DW_Data.mdf',

  SIZE = 10,

  MAXSIZE = 25,

  FILEGROWTH = 15% ),

FILEGROUP DW_Data_Marts

( NAME = DW_Data_Mart_X_Data,

  FILENAME = 'D:\DW_Test_Database\DW_Data_Mart_X_Data.ndf',

  SIZE = 10,

  MAXSIZE = 25,

  FILEGROWTH = 5 ),

( NAME = DW_Data_Mart_Y_Data,

  FILENAME = 'E:\DW_Test_Database\DW_Data_Mart_Y_Data.ndf',

  SIZE = 10,

  MAXSIZE = 25,

  FILEGROWTH = 5 ),

( NAME = DW_Data_Mart_Z_Data,

  FILENAME = 'F:\DW_Test_Database\DW_Data_Mart_Z_Data.ndf',

  SIZE = 10,

  MAXSIZE = 25,

  FILEGROWTH = 5 )

LOG ON

( NAME = DW_Log,

  FILENAME = 'C:\DW_Test_Database\DW_Log.ldf',

  SIZE = 5MB,

  MAXSIZE = 25MB,

  FILEGROWTH = 5MB ) ;

GO

* * * * *


Email Your Comment To AUTHOR