DB snapshots on a partitioned table in simple recovery

· Uncategorized
Authors

Did you know this combination was possible?

Although not tested on partitions using different filegroups. Kind of tricky how DB snapshotfiles will stay in synch with DB files in filegroups.
This script gives you a head start if you want to test it yourself.

USE testPartitioning
go

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2)
CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY])

CREATE TABLE MyPartitionedTable ( i INT , s CHAR(8000) , PartCol INT ) ON MyPartitionScheme (PartCol)

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID(‘MyPartitionedTable’)

INSERT MyPartitionedTable (i, s, PartCol) SELECT 1, ‘a’, 1
INSERT MyPartitionedTable (i, s, PartCol) SELECT 2, ‘a’, 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 3, ‘a’, 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 4, ‘a’, 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 5, ‘a’, 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 6, ‘a’, 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 7, ‘a’, 4

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID(‘MyPartitionedTable’)

— here starts the fun; let’s create a db snapshot and query it

CREATE DATABASE testPartition_dbss ON
( NAME = testPartitioning, FILENAME =
‘C:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testPartition_dbss.ss’ )
AS SNAPSHOT OF [testPartitioning];
GO

select * from testPartition_dbss.dbo.MyPartitionedTable;
select * from testPartitioning.dbo.MyPartitionedTable;

ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3)
ALTER PARTITION FUNCTION MyPartitionRange () merge RANGE (3)
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID(‘MyPartitionedTable’)

CREATE TABLE MyNewPartition ( i INT , s CHAR(8000) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) )

INSERT MyNewPartition SELECT 1, ‘a’, 3

DELETE MyPartitionedTable WHERE PartCol > 2

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID(‘MyPartitionedTable’)
ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID(‘MyPartitionedTable’)

— check our snapshot
select * from testPartition_dbss.dbo.MyPartitionedTable;
select * from testPartitioning.dbo.MyPartitionedTable;

— drop the db snapshot
DROP DATABASE testPartition_dbss

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: