Tuesday, September 18, 2012

Creating a database with multiple file groups











USE [master]
GO

/****** Object:  Database [Testdb]    Script Date: 09/18/2012 16:52:59 ******/
CREATE DATABASE [Testdb] ON  PRIMARY
( NAME = N'Testdb', FILENAME = N'F:\SQLDBs\Testdb.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA]
( NAME = N'Test_Data', FILENAME = N'F:\SQLDBs\Test_Data.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA2]
( NAME = N'Test_Data2', FILENAME = N'F:\SQLDBs\Test_Data2.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_INDEX]
( NAME = N'Test_Index', FILENAME = N'F:\SQLDBs\Test_Index.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'Testdb_log', FILENAME = N'G:\SQLLogs\Testdb_log.ldf' , SIZE = 1048576KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO

ALTER DATABASE [Testdb] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Testdb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO