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 TODISK=N'C:\Backups\demoInternals_Partition2.bak' WITH INIT
GO
BACKUP LOG demoInternals_Partition TODISK=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 1stpartition 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
WRAP IT UP
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
