CODEWITHSUNDEEP
sql-servert-sqlschemafilegroup
Ali Bahraminezhad
Ali Bahraminezhad

Reputation: 326

Create different filegroup for different Schema

I need to create a filegroup for schema I have in SQL Server. The DB is empty and I just want to create schema and their filegroups.

How can I do that?

Upvotes: 1

Views: 3383

Answers (2)

xacinay
xacinay

Reputation: 909

You can create a database group of files with manually specified names: MY_DB_NAME_Data.MDF, MY_DB_NAME_Log.LDF, etc (for SQL Express, search at c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\ ):

DECLARE @sqlcmd nvarchar(1000);
DECLARE @phys_path nvarchar(1000);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @db_name nvarchar(1000);

SET @db_name = N'MY_DB_NAME';

SET @ParmDefinition = N'@phys_pathOUT varchar(1000) OUTPUT';
SET @sqlcmd = 'SELECT @phys_pathOUT = SUBSTRING(physical_name, 1, CHARINDEX('+CHAR(39)+'master.mdf'+CHAR(39)+', LOWER(physical_name)) - 1) 
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1'
DECLARE @CREATE_DATABASE_TEMPLATE VARCHAR(MAX);
SET @CREATE_DATABASE_TEMPLATE = 'CREATE DATABASE ['+ @db_name+ '] ON  PRIMARY 
( NAME = '+CHAR(39)+@db_name+'_Data'+CHAR(39)+', FILENAME = '+CHAR(39)+'{PHYSICAL_PATH}'+@db_name+'_Data.MDF'+CHAR(39)+' , SIZE = 11712KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), FILEGROUP [ARCH] 
( NAME = '+CHAR(39)+@db_name+'_Arch_Data'+CHAR(39)+', FILENAME = '+CHAR(39)+'{PHYSICAL_PATH}'+@db_name+'_Arch_Data.NDF'+CHAR(39)+' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ) LOG ON 
( NAME = '+CHAR(39)+@db_name+'_Log'+CHAR(39)+', FILENAME = '+CHAR(39)+'{PHYSICAL_PATH}'+@db_name+'_Log.LDF'+CHAR(39)+', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
EXEC sp_executesql @sqlcmd, @ParmDefinition, @phys_pathOUT = @phys_path OUTPUT
SET @CREATE_DATABASE_TEMPLATE = REPLACE( @CREATE_DATABASE_TEMPLATE , '{PHYSICAL_PATH}', @phys_path )
EXECUTE( @CREATE_DATABASE_TEMPLATE ) -- creating database at existing physical path location
GO

For creating 'mysch' schema in a database, call:

USE [MY_DB_NAME]
GO
CREATE SCHEMA mysch
GO

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

If I understand your question correctly, you wish that any object created in a particular schema is also added to a particular file group.

AFAIK this isn't possible out of the box - there is a Connect ticket requesting this feature.

Another idea is this one here, viz to use DDL triggers which will prevent objects in a given schema from being created anywhere except a designated file group.

Upvotes: 1

Related Questions