Create different filegroup for different Schema

Question

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?

You cant create schema without creating the database if that is the question. While creating your own database you are able to specify file location for your db. So, please specify which target you wanna hit: scripts for db creation with file location, single schema creation or smth else? — xacinay, May 16 '14 at 13:09
Looking for script to create filegroups and add special Schema to them. — Ali Bahraminezhad, May 16 '14 at 15:07

score 1 · Answer 1 · answered May 16 '14 at 05:56

1

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.

answered May 16 '14 at 05:56

StuartLC

104,537
17
209
285

No, I don't want to do it automatically! How could I do it myself? – Ali Bahraminezhad May 16 '14 at 15:06

score 0 · Answer 2 · answered May 16 '14 at 15:23

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

Create different filegroup for different Schema

2 Answers2