Clean Partition on SQL Server

During the development and testing for Partitions on SQL Server, I have come across this question a lot of time "How do we clear the partition, file, filegroup, partition scheme and function and then start all over again?"

This can be a really tedious process, if you do not do it right and in the right order. This post documents on the steps you need to take to clean everything and start from scratch.

The first thing I always suggest is move all the tables that are on the said partition back to PRIMARY file group. You can check the steps here.

Once you have moved all the tables to the PRIMARY file group, the second step is to remove the Partitioned file groups. Assuming you have a database named TestDB and the Partition Filegroups name is something like TEST_PARTITION_2009_01, TEST_PARTITION_2009_02 and so on for 12 months, run the following syntax

ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_01
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_02
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_03
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_04
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_05
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_06
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_07
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_08
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_09
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_10
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_11
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_12

Third, remove the Partition Scheme and Function. Replace the Partition scheme and Function name accordingly.

DROP PARTITION SCHEME TEST_PARTITION_Part_Sch
DROP PARTITION FUNCTION TEST_PARTITION_Part_Func

Finally, you need to remove the Partition FileGroups from the database. I assume the same naming convention..

ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_01
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_02
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_03
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_04
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_05
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_06
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_07
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_08
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_09
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_10
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_11
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_12

And voila... you have a clean slate now. Enjoy