THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Online restore and filegroups

Is data available for a partitioned table if one of the partitions isn't available? Instead of reading BOL, I wanted to test this. Here is an outline of what I did (you find TSQL code at the end of this post):

  • Create a database over several file groups
  • Create a partitioned table
  • Inserted some rows
  • Database backup
  • Inserted some rows
  • Log backup
  • Inserted some rows
  • Log backup
  • Inserted some rows
  • Database restore of only one file group
  • Answer is yes. If the query is written so that the non-available partition is eliminated (either optimize or run time) we can access the available partitions.
  • Restore of the log backups
    Whole table is now available

TSQL code. Note that this will drop a database named PartOnLineRestore if such exist:

SET NOCOUNT ON
USE master
IF DB_ID('PartOnLineRestore') IS NOT NULL DROP DATABASE PartOnLineRestore
GO
CREATE DATABASE PartOnLineRestore
 ON
 PRIMARY
(NAME = N'PartOnLineRestore', FILENAME = N'C:\PartOnLineRestore.mdf', SIZE = 3MB ),
 FILEGROUP FG1
(NAME = N'FG1File', FILENAME = N'C:\FG1File.ndf', SIZE = 3MB),
 FILEGROUP FG2
(NAME = N'FG2File', FILENAME = N'C:\FG2File.ndf', SIZE = 3MB ),
 FILEGROUP FG3
(NAME = N'FG3File', FILENAME = N'C:\FG3File.ndf', SIZE = 3MB )
 LOG ON
(NAME = N'PartOnLineRestore_log', FILENAME = N'C:\PartOnLineRestore_log.ldf', SIZE = 3MB)
GO
USE PartOnLineRestore

CREATE PARTITION FUNCTION PF1 (int) AS RANGE RIGHT FOR VALUES (1,2);

CREATE PARTITION SCHEME PS1 AS PARTITION PF1  TO (FG1, FG2, FG3);

IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t (a int, b int, c char(300) default 'hello');

-- Populate table with some rows
DECLARE @i int; SET @i=0;
WHILE @i<10000
BEGIN
 SET @i=@i+1;
 INSERT INTO t (a, b) VALUES (5*RAND(),100*RAND())
END;

--Partition table
CREATE CLUSTERED INDEX x ON t(a) ON PS1(a)

--Do some backups
USE master
ALTER DATABASE PartOnLineRestore SET RECOVERY FULL
BACKUP DATABASE PartOnLineRestore TO DISK = 'C:\PartOnLineRestore.bak' WITH INIT
 INSERT INTO PartOnLineRestore..t (a, b) VALUES (5*RAND(),100*RAND())
BACKUP LOG PartOnLineRestore TO DISK = 'C:\PartOnLineRestore.bak' WITH NOINIT
 INSERT INTO PartOnLineRestore..t (a, b) VALUES (5*RAND(),100*RAND())
BACKUP LOG PartOnLineRestore TO DISK = 'C:\PartOnLineRestore.bak' WITH NOINIT
 INSERT INTO PartOnLineRestore..t (a, b) VALUES (5*RAND(),100*RAND())

--Filegroup restore
RESTORE DATABASE PartOnLineRestore FILEGROUP = 'FG1' FROM DISK = 'C:\PartOnLineRestore.bak'

--Can we access the table?
SELECT * FROM PartOnLineRestore..t
--Error message

SELECT * FROM PartOnLineRestore..t WHERE a = 0
--Error message

SELECT * FROM PartOnLineRestore..t WHERE a = 1
--OK, partition elimination at compile time

DECLARE @a int
SET @a = 1
SELECT * FROM PartOnLineRestore..t WHERE a = @a
--OK, partition elimination at run time

--Restore of log backups:
RESTORE LOG PartOnLineRestore FROM DISK = 'C:\PartOnLineRestore.bak' WITH FILE = 2
RESTORE LOG PartOnLineRestore FROM DISK = 'C:\PartOnLineRestore.bak' WITH FILE = 3
BACKUP LOG PartOnLineRestore TO DISK = 'C:\PartOnLineRestore.bak' WITH NOINIT
RESTORE LOG PartOnLineRestore FROM DISK = 'C:\PartOnLineRestore.bak' WITH FILE = 4

--All data vailable?
SELECT * FROM PartOnLineRestore..t WHERE a = 0
--OK

SELECT * FROM PartOnLineRestore..t
--OK

--Cleanup:
DROP DATABASE PartOnLineRestore
 

Published Wednesday, May 02, 2007 7:40 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Be careful!  I was told by a SQL Server PM that this is essentially a side effect and is not guaranteed, documented, or supported behavior.  It just so happens that it works in many cases, but that's not really the intention of partitioning.

May 2, 2007 3:41 PM
 

TiborKaraszi said:

Thanks Adam! OK, this will be filed under the "if you are lucky" category, then :-).

The error message I received for the query without a WHERE clause did indeed imply that this particular query couldn't be executed, but try one which eliminates the problematic partition(s). My interpretation, of course...

May 2, 2007 3:52 PM
 

bertcord said:

This is not good…

Why use partitioned table…hum because I have some very big tables.

Why use online restore….hum because I have some very large tables.

I am going to really need to rethink my approach.  I am thinking perhaps acombo of partitioned tables and partitioned views.

May 4, 2007 5:34 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement