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;

This filegroups are still empty! We need to add datafiles. At least one for each filegroup. Let's do it:

ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'OldStuff', FILENAME = N'S:\Data\OldStuff.ndf', SIZE = 512KB) TO FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2020_Stuff', FILENAME = N'S:\Data\2020_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2021_Stuff', FILENAME = N'S:\Data\2021_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2022_Stuff', FILENAME = N'S:\Data\2022_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2023_Stuff', FILENAME = N'S:\Data\2023_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2023_Stuff;

We need to put data in these files. So we need to prepare a partition function. See Official documentation.

USE [HistoryDemo];
CREATE PARTITION FUNCTION PFStuff (datetime)
AS RANGE RIGHT FOR VALUES ('20200101', '20210101', '20220101', '20230101');

Now we need to map this function to a partition scheme. Also see official documentation.

USE [HistoryDemo];
CREATE PARTITION SCHEME PSStuff  
AS PARTITION PFStuff TO (FGVeryOldStuff, FG_2020_Stuff, FG_2021_Stuff, FG_2022_Stuff, FG_2023_Stuff);

You can see scheme and function in SSMS:

Now we create a simple table:

USE [HistoryDemo];

CREATE TABLE dbo.big_one
(
	id int NOT NULL IDENTITY (1, 1),
	event_time datetime NOT NULL,
	some_text nchar(2000) NULL
)
ON PSStuff(event_time);
ALTER TABLE dbo.big_one
   ADD CONSTRAINT PK_big_one PRIMARY KEY CLUSTERED (id, event_time);

This is not nice, loot at primary key. Not elegant, but not a problem for a demo. It is important to notice this line:

ON PSStuff(event_time);

It means that table will relay on partion scheme PSStuff.

Populate table big_one with some data, in order to have few data in every partition. This is not necessary, we just want to see our data restored!

INSERT INTO big_one (event_time, some_text) VALUES ('20180501 12:30', 'Good lunch with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20180501 20:30', 'Good dinner with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20200401 06:00', 'Epic prank to Dimitri');
INSERT INTO big_one (event_time, some_text) VALUES ('20200402 20:30', 'Dinner offered to Dimitri to apologize');
INSERT INTO big_one (event_time, some_text) VALUES ('20211231 23:50', 'This year was boring');
INSERT INTO big_one (event_time, some_text) VALUES ('20220915 16:00', 'Had a tea with boss');
INSERT INTO big_one (event_time, some_text) VALUES ('20230101 13:00', 'Lunch with family. Really???');
INSERT INTO big_one (event_time, some_text) VALUES ('20230701 00:11', 'Reading future');