Sarah Henwood : multiple filegroups

Validating What is Stored in a Partition/Filegroup

sarahhen — Mon, 23 Jul 2007 13:13:00 GMT

How do you validate what is stored in a specific partition and the filegroup the partition resides on? I have found this handy when designing a partition strategy and to double check it is behaving as I expect and my data is stored correctly for the requirements. It also can help if you document as part of your disaster recovery planning, you can then quickly refer to your documentation and identify the specific filegroup you need to restore to recover specific partitions.

For example, you have a data warehouse that stores previous fiscal year data by quarter for Fiscal Year 2005 through 2006. Your partition function and scheme are defined as follows (see end of notes if you want the filegroup creation so you can step through these commands):

create partition function pf_MyFyQuarters (datetime) as

range right for values

(

'2004-10-01 00:00:00','2005-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00', -- 2005

'2005-10-01 00:00:00','2006-01-01 00:00:00','2006-04-01 00:00:00','2006-07-01 00:00:00') -- 2006

create partition scheme ps_MyFyQuarters

as partition pf_MyFyQuarters

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9

fgAfter2006) -- partition 10

When SQL creates the partition scheme, it maps in order each partition to the filegroup(s) listed and each are numbered in order as the comments demonstrate above. In this example, because the partition function is defined with a 'range right' - partition 1 (assigned to file group fgBefore2005) stores all values less than 10/1/2004. Partition 2 (assigned to file group fg2005Quarter1) stores values >= 10/1/2004 and < 01/01/2005 and so on through partition 10 (assigned to fgAfter2006) that stores values >= 07/01/2006.

Next, to validate the scenario, create a table on the partition scheme with some data:

CREATE TABLE [dbo].[PartitionTable]

(

[DateColumn] [datetime] NULL

)

ON ps_MyFyQuarters (DateColumn)

insert PartitionTable

Values('09/30/2004') -- This will go on partition 1 (file group fgBefore2000)

insert PartitionTable

Values('09/30/2005') -- partition 5 (file group fg2005Quarter4)

insert PartitionTable

Values('10/01/2005') -- partition 6 (file group fg2006Quarter1)

insert PartitionTable

Values('03/31/2006') -- partition 7 (file group fg2006Quarter2)

insert PartitionTable

Values('07/04/2006') -- partition 9 (file group fg2006Quarter4)

You can then use the $PARTITION function to validate what is actually stored by the partition number and the corresponding filegroup based on what you defined in your partition scheme earlier:

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 1

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 5

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 6

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 7

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 9

You can also query from the DMV to list out the partition/scheme and validate what is stored on your filegroups. For example, another administrator created another partition and scheme - but accidentally stores it on the same filegroups. Note what the DMV query returns after you create another partition function and scheme:

create partition function pf_MyFyQuarters_B (datetime) as

range right for values

(

'2002-10-01 00:00:00','2003-01-01 00:00:00','2003-04-01 00:00:00','2003-07-01 00:00:00', -- 2003

'2003-10-01 00:00:00','2004-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00') -- 2004

create partition scheme ps_MyFyQuarters_B

as partition pf_MyFyQuarters_B

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5:

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9:

fgAfter2006) -- partition 10:

-- this DMV query will list out what schemes and partition numbers are stored on your filegroups

-- note the different scheme and partition on the same filegroups

select ds.name AS [Filegroup Name], ds.type, destination_id AS [partition number], dds.partition_scheme_id, ps.name as [partition scheme]

from sys.data_spaces ds

join sys.destination_data_spaces dds

on (ds.data_space_id = dds.data_space_id)

join sys.partition_schemes ps

on (ps.data_space_id = dds.partition_scheme_id)

order by ds.name, ps.name ASC

--------- File/Filegroup Script For Examples Above --------------

create database my_db

-- Add filegroups that will contains partitioned values

alter database my_db add filegroup fgBefore2005;

alter database my_db add filegroup fg2005Quarter1;

alter database my_db add filegroup fg2005Quarter2;

alter database my_db add filegroup fg2005Quarter3;

alter database my_db add filegroup fg2005Quarter4;

alter database my_db add filegroup fg2006Quarter1;

alter database my_db add filegroup fg2006Quarter2;

alter database my_db add filegroup fg2006Quarter3;

alter database my_db add filegroup fg2006Quarter4;

alter database my_db add filegroup fgAfter2006;

-- Add files to filegroups

alter database my_db add file (name = 'fF05Q1', filename = 'C:\fF05Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter1;

alter database my_db add file (name = 'fF05Q2', filename = 'C:\fF05Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter2;

alter database my_db add file (name = 'fF05Q3', filename = 'C:\fF05Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter3;

alter database my_db add file (name = 'fF05Q4', filename = 'C:\fF05Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter4;

alter database my_db add file (name = 'fF06Q1', filename = 'C:\fF06Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter1;

alter database my_db add file (name = 'fF06Q2', filename = 'C:\fF06Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter2;

alter database my_db add file (name = 'fF06Q3', filename = 'C:\fF06Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter3;

alter database my_db add file (name = 'fF06Q4', filename = 'C:\fF06Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter4;

alter database my_db add file (name = 'fBefore05', filename = 'C:\fBefore05.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgBefore2005;

alter database my_db add file (name = 'fAfter06', filename = 'C:\fAfter06.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgAfter2006;

How to: Partial Restore After Media/Drive Failure

sarahhen — Mon, 23 Jul 2007 13:06:00 GMT

Recently I had a few questions about how to go about partial restores if you have media failure that affects only specific filegroups, and your database is inaccessible because of the missing file(s). The steps while simple, are not readily apparent in example form in books online.

In this example you have a multi filegroup database in bulk_logged recovery model. The database consists of read/write and read only filegroups. I throw these factors in to add a little bit more variance to the example.

-- create example database and filegroups

CREATE DATABASE my_db

ALTER DATABASE my_db

SET RECOVERY bulk_logged

alter database my_db add filegroup fg_static_A;

alter database my_db add filegroup fg_static_B;

alter database my_db add filegroup fg_static_C;

alter database my_db add filegroup fg_readwrite_A;

alter database my_db add file (name = 'f_static_A', filename = 'C:\f_static_a.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_A;

alter database my_db add file (name = 'f_static_B', filename = 'C:\f_static_b.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_B;

alter database my_db add file (name = 'f_static_C', filename = 'C:\f_static_c.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_C;

alter database my_db add file (name = 'f_readwrite_A', filename = 'C:\f_readwrite_A.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_readwrite_A;

You have done some bulk loads of static data to partitions in specific filegroups. You then mark these filegroups as read only:

ALTER DATABASE my_db

MODIFY FILEGROUP fg_static_A READ_ONLY

ALTER DATABASE my_db

MODIFY FILEGROUP fg_static_B READ_ONLY

Your next scheduled full backup occurs. The full backup captures both read only and read/write filegroups:

BACKUP DATABASE my_db

TO DISK='C:\my_db_full.bak' WITH INIT

You then do some more bulk loads of static data and mark that specific filegroup as read only:

ALTER DATABASE my_db

MODIFY FILEGROUP fg_static_C READ_ONLY

You don't have the maintenance window right now to do perform a differentail or full file backup, and decide to do a transaction log backup. At this point, you will want to switch to full recovery before your next transaction log backup, take the transaction log backup and then switch back to bulk_logged. Otherwise, your bulk logged extents will keep getting backed up for your read only filegroups.

ALTER DATABASE my_db

SET RECOVERY FULL

BACKUP LOG my_db

TO DISK='c:\my_db_log.bak' with init

ALTER DATABASE my_db

SET RECOVERY BULK_LOGGED

Your database continues to be used and data is inserted/modified in the read/write filegroups. You then perform a partial backup of just your read/write filegroups:

BACKUP DATABASE my_db

READ_WRITE_FILEGROUPS

TO DISK='C:\my_db_read_write.bak' with init

More data modification activity occurs on the database and then unfortunately you have drive failure that results in one of your read/write filegroups to disappear. SQL Server was restarted and queries on the database fail because the file is missing.

To simulate this, stop SQL, move file f_read_write_A somewhere else and restart SQL. Try quering the database, it should fail because of the missing file.

What do you do next if you want to just restore the read/write filegroup in question and get your database back online as quick as possible?

-- #1 back up the tail of the log with no_truncate. You *must* do this before you can do anything else.

BACKUP LOG my_db

TO DISK='c:\my_db_log_tail.bak' wth init

with NO_TRUNCATE

-- #2 then restore just the damaged file(s)/filegroup from the *partial* backup you took earlier

RESTORE DATABASE my_db FILE='f_readwrite_A' FROM DISK='C:\my_db_read_write.bak'

WITH NORECOVERY

-- #3 restore all transaction log backup sequences ending with the latest tail you backed up.

RESTORE LOG my_db

from DISK='c:\my_db_log.bak'

with norecovery

RESTORE LOG my_db

from DISK='c:\my_db_log_tail.bak'

with recovery

Your database is now back online.

However, what if you lost one of your read only filegroups instead? For example, FILEGROUP fg_static_C. In this scenario, keep in mind that you've not yet performed a differentail or full backup since populating that filegroup and marking it read only. What steps would be required to restore that filegroup?

-- #1 back up the tail of the log with no_truncate. You *must* do this before you can do anything else.

BACKUP LOG my_db

TO DISK='c:\my_db_log_tail.bak'

with NO_TRUNCATE, init

-- #2 Start your restore sequence from the last full backup. In this scenario, you must go back to the full backup because the partial only captured the readwrite filegroups.

RESTORE DATABASE my_db FILE='f_static_C' FROM DISK='C:\my_db_full.bak'

WITH NORECOVERY

-- #3 restore all transaction log backup sequences ending with the latest tail you backed up.

RESTORE LOG my_db

from DISK='c:\my_db_log.bak'

with norecovery

RESTORE LOG my_db

from DISK='c:\my_db_log_tail.bak'

with recovery

Your database is now back online and ready to go!