SQL Filegroups

I create a demo SQL script to demonstrate how to use filegroups, how to place data and index on different filegroups, and how to backup and restore filegroups. I believe the script and the comments will help people understand some of the concepts of designing very large databases. A working sample is better than a thousand words.

First let’s create a testing database called TEST

CREATE DATABASE TEST
ON ( NAME = 'TEST_DATA', FILENAME = 'D:\TEST_DATA.MDF' )
LOG ON ( NAME = 'TEST_LOG', FILENAME = 'F:\TEST_LOG.LDF' )

We didn’t specify options other than the data file
and log file. By default the first data file belongs to the primary file group, the database has a full recovery model and the files will grow automatically by 10 percent each time with no maximum file size restriction. You can place the log file on a different drive and usually the drive should be fault tolerance.

Suppose we want to separate data and index on different files. We can create two filegroups, one for data and one for index. We can also assign two or more physical files to each filegroups. To achieve better performance we can place the files in a filegroup on different drives

ALTER DATABASE TEST ADD FILEGROUP TestDataGroup
ALTER DATABASE TEST ADD FILE ( NAME = Testdatafile1, FILENAME = 'D:\Testdatafile1_data.NDF' ) TO FILEGROUP TestDataGroup
ALTER DATABASE TEST ADD FILE ( NAME = Testdatafile2, FILENAME = 'E:\Testdatafile2_data.NDF' ) TO FILEGROUP TestDataGroup

ALTER DATABASE TEST ADD FILEGROUP TestIndexGroup
ALTER DATABASE TEST ADD FILE ( NAME = Testindexfile1, FILENAME = 'D:\TestIndexfile1_data.NDF' ) TO FILEGROUP TestIndexGroup
ALTER DATABASE TEST ADD FILE ( NAME = Testindexfile2, FILENAME = 'E:\TestIndexfile2_data.NDF' ) TO FILEGROUP TestIndexGroup

Now we can create a table called test on the TestDataGroup and create an index of the test table on the TestIndexGroup

CREATE TABLE test (recordid int, firstname varchar(20), lastname varchar(20)) on TestDataGroup
CREATE Index ix_recordid on test(recordid) ON TestIndexGroup

Now lets backup the database. Suppose your database grow very large and it take a long time to backup the whole database. You can separate you backup into different backup task by backing up one filegroup each time.

BACKUP DATABASE TEST FILEGROUP = 'Primary' TO DISK='C:\primiarybackup' WITH INIT
BACKUP DATABASE TEST FILEGROUP = 'TestDataGroup' TO DISK='C:\databackup' WITH INIT
BACKUP DATABASE TEST FILEGROUP = 'TestIndexGroup' TO DISK='C:\indexbackup' WITH INIT
BACKUP LOG TEST TO DISK='C:\logbackup' WITH INIT

The option WITH INIT tells SQL server to overwrite the backup file instead of appending to it. Remember to backup the log file if any changes is made after you backup a filegroup otherwise database can not be recovery during a restoration.

Now let’s restore the TEST database as TESTCOPY. We have to restore the primary filegroup first

RESTORE DATABASE TESTCOPY FILEGROUP = 'Primary' FROM DISK='c:\primiarybackup'
WITH MOVE 'test_Data' TO 'C:\testcopy_data.mdf', MOVE 'test_Log' TO 'C:\testcopy_log.LDF'

Then we restore the data file group.

RESTORE DATABASE TESTCOPY FILEGROUP = 'TestDataGroup' FROM DISK='c:\databackup'
WITH MOVE 'Testdatafile1' TO 'c:\Testdatafile1copy_data.NDF',
MOVE 'Testdatafile2' TO 'C:\Testdatafile2copy_data.NDF',
MOVE 'test_Log' TO 'C:\testcopy_log.LDF'

Restore the index file group

RESTORE DATABASE TESTCOPY FILEGROUP = 'TestIndexGroup' FROM DISK='c:\indexbackup'
WITH MOVE 'Testindexfile1' TO 'c:\Testindexfile1copy_data.NDF',
MOVE 'Testindexfile2' TO 'C:\Testindexfile2copy_data.NDF',
MOVE 'test_Log' TO 'C:\testcopy_log.LDF'

Last restore the log file and recover the database

RESTORE LOG test1 FROM DISK='c:\logbackup' WITH RECOVERY,
MOVE 'test_Data' TO 'C:\testcopy_data.mdf',
MOVE 'Testdatafile1' TO 'C:\Testdatafile1copy_data.NDF',
MOVE 'Testdatafile2' TO 'C:\Testdatafile2copy_data.NDF',
MOVE 'Testindexfile1' TO 'C:\Testindexfile1copy_data.NDF',
MOVE 'Testindexfile2' TO 'C:\Testindexfile2copy_data.NDF',
MOVE 'test_Log' TO 'C:\testcopy_log.LDF'