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.