I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition.
I've inherited a large customer metrics database that grows by about 10 GB per day. This database currently consists of one large data file in one filegroup (PRIMARY). All tables have a datetime column called InsertedDate
. I want to horizontally partition the data by InsertedDate
, using a separate data file for each calendar week.
In a test environment I added the required additional filegroups and data files to this database, put a clustered index on InsertedDate
in each table, and set up the partition function and partition scheme. By querying sys.partitions
and other system tables, I've confirmed that the data is now physically residing in the correct partitions and data files.
Among others, the goals are:
Decrease backup time by only backing up the
PRIMARY
file group and the file group for the current date range (I'm currently running nightly full backups). Once a date range is in the past, that partition will never be written to again, so I'd like to set the filegroup to read-only and back it up one final time.Be able to eventually take a partition "out of service". After 3 months there's no longer a need to keep older data online, so I'd like to take take that data offline (but be able to bring that data back online again if necessary).
Questions:
1) How to I perform backups on this partitioned database without having to back up the entire database? I can back up an individual filegroup, but to restore it requires the rest of the database, which defeats the purpose of using multiple smaller data files.
2) How to I take a partition out of service? I've read about switching, but it seems that only works if you want to move data between partitions within the same database. I want to be able to simply and safely take a range of data offline (and bring it back online of necessary).