Backup and restore huge partitioned database - SQL Server

This example fits the case when you have a big database in SQL Server where:

• one ore more table are partitioned
• one or more filegroup are in readonly, for example filegroups with old data

Suggestion: when you have a big database with historical data, it is a good idea to design it similar to this example.

In this case you can backup and restore filegroups separately. If you are doing full backups of this kind of database you are wasting time, space and power. You are wasting money.

Demo database creation

We are going to create a simple database:

CREATE DATABASE [HistoryDemo]
ON  PRIMARY 
(NAME = N'HistoryDemo', FILENAME = N'S:\Data\HistoryDemo.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON 
(NAME = N'HistoryDemo_log', FILENAME = N'L:\Log\HistoryDemo_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

Now we add filegroups where partitioned data will relay:

ALTER DATABASE [HistoryDemo] ADD FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2023_Stuff;