Author: Tibor Karaszi

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

You might know that we have some new options for backup and especially restore in SQL Server 2005. Below script was inspired by a newsgroup post regarding not being able to restore an old readonly filegroup backup into a new database.

Here is what I wanted to try:

• Setting a filegroup to read only, do a backup and then forget about this filegroup. This was not an option in earlier releases. In 7.0 and 2000, restoring a file or filegroup backup meant that you also need to restore all subsequent log backups. Not very convenient if it is Dec 10 and the backup was performend Jan 01. We can do this now, but the filegroup must have been readonly all the time since the backup of that filegroup was performed.
• Piecemal restore. This allow you to first restore the primary filegroup, and immediately after let the users in to the database. You can now, at your leisure, restore the other filegroups. This allow for the users to more quickly get into the database. They don’t have to wait 10 hours for you to restore that large history table, which is only used for reporting anyhow.
• Do above into a new database name. Say that you had to rebuild the old server and only have the backup files available.

Below is the script. Note that it will drop the databases X and X2, if such exists:

USE master
GO
IF DB_ID(‘x’) IS NOT NULL DROP DATABASE x
GO
IF DB_ID(‘x2′) IS NOT NULL DROP DATABASE x2
GO
CREATE DATABASE [x] ON  PRIMARY
( NAME = N’x’, FILENAME = N’C:\x.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [STUFF]
( NAME = N’x_stuff’, FILENAME = N’C:\x_stuff.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’x_log’, FILENAME = N’C:\x_log.ldf’ , SIZE = 3072KB , FILEGROWTH = 10%)
GO

CREATE TABLE x..t(c1 int) ON “PRIMARY”
INSERT INTO x..t VALUES(1)
CREATE TABLE x..t_stuff(c1 int) ON STUFF
INSERT INTO x..t_stuff VALUES(1)
GO
ALTER DATABASE x MODIFY FILEGROUP STUFF READONLY
GO
BACKUP DATABASE x FILEGROUP = ‘STUFF’ TO DISK = ‘C:\x_fg_STUFF.BAK’ WITH INIT
GO
INSERT INTO x..t VALUES(2)
GO
BACKUP DATABASE x READ_WRITE_FILEGROUPS TO DISK = ‘C:\x_fg_rw.BAK’ WITH INIT
GO

–Restore the primary (read/write) filegroup
RESTORE DATABASE x2 FROM DISK = ‘C:\x_fg_rw.BAK’
WITH
PARTIAL
,MOVE ‘x’ TO ‘C:\x2.mdf’
,MOVE ‘x_stuff’ TO ‘C:\x2_stuff.mdf’
,MOVE ‘x_log’ TO ‘C:\x2_log.mdf’
,REPLACE

SELECT * FROM x2..t –OK
GO
SELECT * FROM x2..t_stuff –Fails, as expected
GO

–Restore the other filegroup
RESTORE DATABASE x2 FROM DISK = ‘C:\x_fg_STUFF.BAK’
WITH
MOVE ‘x’ TO ‘C:\x2.mdf’
,MOVE ‘x_stuff’ TO ‘C:\x2_stuff.mdf’
,MOVE ‘x_log’ TO ‘C:\x2_log.mdf’
GO

SELECT * FROM x2..t –OK
SELECT * FROM x2..t_stuff –OK