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

Leave a Reply

Your email address will not be published.