Piecemeal Database Restore
Published: May 21, 2017
This is a direct follow up from my last post about filegroups because without those we wouldn’t be able to perform what is called a Piecemeal database restore and, trust me, this is something you want to know how to do.

Basically this allows us to restore just certain filegroups from a database rather than the entire thing. This means that you could get a large database online quickly and then backfill later (vital in an emergency), or you might have a 1TB database with data going back to 2001, but in test you only want data from 2015… this would allow you to do just that.

Note that with a piecemeal restore you HAVE to restore the PRIMARY filegroup and therefore you should make this either as small as possible, or just use it to hold your generic tables, not those with date based (for example) data which you want to restore individually.

So let’s just get straight into this with a demo…

I’m going to create a database with the PRIMARY filegroup alongside 2013, 2014, and 2015 filegroups. I’m then going to create a single table in PRIMARY and then one table over all other filegroups.

The latter could be achieved with a Partitioned View, but I’m going to use an actual partitioned table.

Then we’ll enter some test data, backup the database, drop the database (pretending we’ve moved to another server, for example) and then restore just the PRIMARY and 2015 filegroups…

First we create our database:

use master
go

if exists
(
  
select *
  
from sys.databases
  
where name = 'piecemealRestore'
)
begin
   alter database
piecemealRestore set single_user with rollback immediate

   drop database
piecemealRestore
end
go

create database piecemealRestore on primary
(
  
name = 'piecemealRestore',
  
filename = 'D:\SQLData\piecemealRestore.mdf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
)
log on
(
  
name = 'piecemealRestore_Log',
  
filename = 'D:\SQLData\piecemealRestore_log.ldf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
)
go


Next we add our filegroups:

alter database piecemealRestore add filegroup piecemealRestore2013
alter database piecemealRestore add filegroup piecemealRestore2014
alter database piecemealRestore add filegroup piecemealRestore2015
go


Then add our files to our filegroups:

alter database piecemealRestore
add file
(
  
name = 'piecemealRestore2013',
  
filename = 'D:\SQLData\piecemealRestore2013.ndf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
) to filegroup piecemealRestore2013
go

alter database piecemealRestore
add file
(
  
name = 'piecemealRestore2014',
  
filename = 'D:\SQLData\piecemealRestore2014.ndf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
) to filegroup piecemealRestore2014
go

alter database piecemealRestore
add file
(
  
name = 'piecemealRestore2015',
  
filename = 'D:\SQLData\piecemealRestore2015.ndf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
) to filegroup piecemealRestore2015
go


Now we need our partition function and scheme:

use piecemealRestore
go

if exists
(
  
select *
  
from sys.partition_functions
  
where name = 'pf_piecemeal'
)
begin
   drop
partition function pf_piecemeal
end

create
partition function pf_piecemeal(date)
  
as range left
  
for values('2014-01-01', '2015-01-01')
go

if exists
(
  
select *
  
from sys.partition_schemes
  
where name = 'ps_piecemeal'
)
begin
   drop
partition scheme ps_piecemeal
end

create
partition scheme ps_piecemeal
  
as partition pf_piecemeal to (piecemealRestore2013, piecemealRestore2014, piecemealRestore2015)
go


Now we’ll create a couple of tables, our generic, and our partitioned:

if object_id('genericTable') is not null drop table genericTable
go

create table genericTable
(
  
id int identity,
  
value char(100) default('a')
)
on [primary]
go

insert into genericTable default values
go 100

if object_id('partitionTable') is not null drop table partitionTable
go

create table partitionTable
(
  
myDate date,
  
myValue char(100) default('b')
)
on ps_piecemeal(myDate)
go

insert into partitionTable(myDate) values('2013-06-01'), ('2014-06-01'), ('2015-06-01')
go


All that’s left is to take our backup and then move to another server (or in my case just drop the database):

backup database piecemealRestore
to disk = 'D:\SQLBackup\piecemealRestore.bak'
with stats = 1
go

use master
go

alter database piecemealRestore set single_user with rollback immediate
go

drop database piecemealRestore
go


Okay, now what we need to do is to restore this database… but we’re only going to restore 2015 and therefore leave ourselves with a much smaller database in our test environment (in the real world… obviously it’s irrelevant in my tiny database example):

restore database piecemealRestore
   filegroup
= 'Primary'
from disk = 'D:\SQLBackup\piecemealRestore.bak'
with partial, norecovery
go

restore database piecemealRestore
   filegroup
= 'piecemealRestore2015'
from disk = 'D:\SQLBackup\piecemealRestore.bak'
with norecovery
go

restore log piecemealRestore
from disk = 'D:\SQLBackup\piecemealRestore.bak'
with norecovery
go

restore database piecemealRestore
with recovery
go


And now you can query your data again as long as you use the correct filters, for example this won’t work:

use piecemealRestore
go

select *
from partitionTable
where myDate <= '2014-01-01'
go


However, these queries will return just fine:

use piecemealRestore
go

select top 1 *
from genericTable
go

select *
from partitionTable
where myDate > '2015-02-01'
go


It’s as simple as that.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron