Hello Dear Reader! This week
finds me up in Jacksonville at Pragmatic Works HQ. I'm a little behind in
getting the blog up for last week Webinar on Partitioning in SQL Server 2012.
We covered a lot of great things in the webinar and I wanted
to recap some of them. This was the
first time I’d given this presentation and over 300 people tuned in to watch!
I’d like to say a quick Thank You
to all the people that spent their hard earned time with me. If you would like to download anything from
the presentation Click
Here for the Deck, Click
Here for the Demo Scripts, and click
here to watch the video recording of the presentation. All of this is also up on my Resource page.
I had a hiccup on the Piecemeal
Restore Demo that I did and I wanted to review it. Unfortunately I was playing around with the
script and just to be on the safe side had backed up my data base. At the header of the script I inserted a
restore command. I got an error running
the script and wanted to fit a couple other demos in and skipped over it. So now we can tackle it.
“So Balls”, you say, “What’s a Piecemeal Restore and WHY would I
need to use one?”
Excellent question Dear Reader!
The Piecemeal restore was introduced in SQL 2005. It gave us a sequence of steps we could take
in order to recover a portion of a database online at a time starting with the
Primary Filegroup. This allows us to
bring critical portions of the database online for quick access.
If you had a very large database
with a lot of historical data you wouldn’t want to make the business stay
offline in a critical outage while all the historical data is restored. If you have TB’s of data that could take
hours!
A Piecemeal restore gives us the
ability to bring a segment of the database online at a time. A very easy way to demonstrate this is using
partitioning.
DEMO TAKE II
Make sure to use 02 Demo_a Set Up demoInternals_Partition.sql
to set up our demoInternals_Partition Database in the scripts above.
First let’s take a look at our table. We will use sys.partitions and sys.indexes to
see how the data is distributed across filegroups.
This will also let us see the Clustered Index
and Non-Clustered Index we created.
SELECT
OBJECT_NAME(sp.object_id) AS tableName,
si.name AS indexName,
sp.partition_number,
sp.rows
FROM
sys.partitions sp
JOIN sys.indexes si
ON si.object_id=sp.object_id AND si.index_id =sp.index_id
WHERE OBJECT_NAME(sp.object_id) ='myTable1';
You can see from the count we have 18,000 rows in our
table. Now that we’ve looked at our data the next thing I need to do is backup
my database. I’m going to perform 3
types of backups. First a Full backup,
secondly I’m going to perform a Log Backup, third we’ll perform a tail of the
log backup and leave our database in a restoring state.
*NOTE* You can use a Piecemeal restore with all recovery
models however I’m running in Full recovery for the sake of the demos today.
USE master
go
BACKUP DATABASE demoInternals_Partition TO
DISK=N'C:\Backups\demoInternals_Partition2.bak' WITH INIT
GO
BACKUP LOG demoInternals_Partition TO
DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH INIT
GO
BACKUP LOG [demoInternals_Partition] TO DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'
WITH NO_TRUNCATE , INIT, NORECOVERY
GO
Looking at our database I can see that the commands have
completed and we are in the Restoring state.
Our database is completely inaccessible, I know I know snapshots…. But
that’s not the point so stick with me Dear Reader. First let’s restore our primary data file.
USE master
GO
RESTORE DATABASE demoInternals_Partition FILEGroup='primary'
FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH PARTIAL, NORECOVERY
GO
RESTORE LOG demoInternals_Partition FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery
RESTORE LOG [demoInternals_Partition] FROM DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
If I refresh my SSMS Object Explorer Window it looks like
the database is back online. However, I
know better. The only filegroup online
is the Primary Filegroup. I like to keep
this filegroup small with only the metadata that is there when the database is
created. If I try to query the table
dbo.mytable1 it should fail. Let’s do
that real quick. We’ll query one of our
DMV’s about our table that we cannot acces, let’s say sys.indexes. Then we’ll do a very simple query against the
database to get record 1. Remember our 1st
partition had 2000 rows in it.
use demoInternals_Partition
go
select
object_name(si.object_id)
,si.name
,si.type_desc
,si.name
from
sys.indexes si
where
object_name(si.object_id)='mytable1'
select
*
from
dbo.myTable1
where
myid=1
As you can see we got results from our DMV, but we couldn’t
even access row 1 in our table. Now let’s
bring FG1 online.
use master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG1'
FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
GO
RESTORE LOG demoInternals_Partition FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery
RESTORE LOG [demoInternals_Partition] FROM DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
Now let’s try our query again.
USE demoInternals_Partition
GO
select
*
from
dbo.myTable1
where
myid=1
Success. We can get all 2000 rows in the FG1 partition. If
you want to get the same error as before for FG2, just change the 1 to a
2001. This is a very flexible process
that allows you to assign Business level SLA’s to different segments of your
Database. You do not need to use
partitioning to do a piecemeal restore.
You could just use separate FG’s and segment tables by business segment.
Let’s bring online FG2 and FG6, leaving FG3, FG4, and FG5
still offline.
USE master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG2'
FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
RESTORE DATABASE demoInternals_Partition FILEGroup='FG6'
FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
RESTORE LOG demoInternals_Partition FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery
RESTORE LOG [demoInternals_Partition] FROM DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
Now’ let’s execute the following queries:
use demoInternals_Partition
go
select * from dbo.mytable1 as FG2 Where FG2.myid=2500
select * from dbo.mytable1 as FG6 Where FG6.myid=12001
go
select * from dbo.mytable1 as FG3 Where FG3.myid=4500
select * from dbo.mytable1 as FG5 Where FG5.myid=8000
The Queries against FG2, and FG 6 Succeed. The Queries against FG3 and FG5 failed. Okay now let’s bring all the tables online.
USE master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG3'
FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
RESTORE DATABASE demoInternals_Partition FILEGroup='FG4'
FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
RESTORE DATABASE demoInternals_Partition FILEGroup='FG5'
FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
RESTORE LOG demoInternals_Partition FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery
RESTORE LOG [demoInternals_Partition] FROM DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
We can now query from start to finish with the entire
database online. This is a pretty
simplistic demo. It is meant just to
convey the different architectural options that are available for a Piecemeal
restore. As you can imagine this is
something that could be utilized in a DR scenario to meet SLA’s and RTO.
This is a very powerful tool in the arsenal of the DBA. You want to test this, and make sure that it meets your business needs before implementing it. Don't forget to get the demo's and scripts from the presentation.
As always Dear Reader, Thanks for stopping by!
Thanks,
Brad
