Monday, May 14, 2012

Basic walkthrough of Partial Backup and Restore for Partitioned and Read Only SQL Server Databases

Hi all,
Building on an excellent article from Pinal Dave, here is a walkthrough test of creating, partioning, backing up and restoring (partial and full) of a sql server database.  These tests were done on sql 2008 but should be pretty generic.

What I wanted to test is the ability to make parts of a large database read only and stop backing up the whole damn thing.  The basic steps (detail below) are:

  1. Create partitioned table.  This can be a new table and database (as below) or you can repartition an existing table (create your partition function and scheme and apply a clustered index).  Note that repartitioning can take a long time and locks your table so test accordingly.
  2. Set file group read only
  3. Backup ReadWrite and ReadOnly filegroups separately.
  4. Restore (Primary and) ReadWrite Filegroup (FG) in new db 
  5. Restore ReadOnly filegroup
  6. Verify
  7. Make new Filegroup read only in original DB
  8. New backup of  ReadWrite Filegroup and new backup of new ReadOnly  file group
  9. Restore  ReadWrite Filegroup, tell database your first ReadOnly filegroup is in a good state already and to just recover it, restore newly ReadOnly Filegroup.
  10. Verify
I do that a few times to verify the procedure and then have a disaster recovery version at the bottom.  Below is a step by step walkthrough.

First we create the test database, the partition function, scheme and table.  We then insert data that will span several partitions.  All of this is stolen more or less directly from Pinal Dave, I had more steps to test so I added a few more partitions.  Note, if you don't have a d drive, you will need to update the paths accordingly.

Update: see here for details on differential backups.

-- Step 1: setup basic test database
use master
exec master.dbo.usp_killconnections 'testdb'
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1', FILENAME='D:\data\TestDB_Part1.mdf',
SIZE=3, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2', FILENAME ='D:\data\TestDB_Part2.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part3
(NAME = 'TestDB_Part3', FILENAME ='D:\data\TestDB_Part3.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part4
(NAME = 'TestDB_Part4', FILENAME ='D:\data\TestDB_Part4.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part5
(NAME = 'TestDB_Part5', FILENAME ='D:\data\TestDB_Part5.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );
GO



--- Step 2 : Create Partition Range Function
USE TestDB;
GOCREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (10,20,30,40);
GO



USE TestDB;
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2, TestDB_Part3, TestDB_Part4, TestDB_Part5);
GO

--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO

--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4
VALUES (32,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (42,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (52,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3
VALUES (22,GETDATE());
GO

--- Step 7 : Test Data from TestTable
SELECT *
FROM TestTable;
GO

--- Step 8 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO



We can do two types of backups now.  We can do a standard full backup which is fine on this size of database but as the dbs get large, syncing around and restoring very large dbs can be a PITA.  If we can make some of our data read only (with all that entails), we can speed our backups, restores and copy times dramatically.

First, we want to set partition 2 to ReadOnly.  We do that with 
-- set FG 2 to read only
ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part2] READONLY
GO

We can now backup the RW FG's separately

-- Backup rw and ro separately
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'D:\data\TestDB_RW.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TestDB]
FILEGROUP = N'TestDB_Part2'
TO  DISK = N'D:\data\TestDB_RO_Part2.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

We have 2 backup files now.  Check the IsPresent column for each FileGroup with:
-- list backup files
RESTORE FILELISTONLY from disk =  N'D:\data\TestDB_RW.bak'
RESTORE FILELISTONLY from disk =  N'D:\data\TestDB_RO_Part2.bak'

We can now begin our restore on the TestDB2 (imaginatively named, i know).  First the RW filegroup (which, importantly, contains the PRIMARY FG)
--restore RW
use master
exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10
GO

All, well and good but if we check our table, we get an error as not all the data is present.  One important note that is not shown below is that if we had another table in our database, we could query that w/ no problem.  It is only queries that try to hit tables w/ missing data that fail.  
--- Verify (should fail)
use testdb2

SELECT *
FROM TestTable;
GO

To see why this fails run: 
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')


Note that our RO group is offline.  We need to restore it.
-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM
DISK = N'D:\data\TestDB_RO_Part2.bak'   WITH 
MOVE 'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf'

Now we can verify

--- Verify (should succeed)
use testdb2

SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')


Lets add some more data to verify that we are restoring the correct backups.  Remember that you cannot add any data to Partition 2!
-- add data
use testdb

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4
VALUES (33,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (43,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (53,GETDATE());


Now we want to test what happens when we backup and restore a copy of the RW data.  We don't want to have to deal w/ the RO data
-- Backup rw
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'D:\data\TestDB_RW2.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



--restore RW
use master
exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW2.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10
GO
 --- Verify (should succeed)
use testdb2

SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')

Now we set another file to RO
-- set FG 3 to read only

use master
exec master.dbo.usp_killconnections [TestDB]
ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part3] READONLY
GO

You may be tempted to just send the RO backup over but you need to send a new backup of the PRIMARY FG w/ the meta data about the RO FG.  So we backup RW and the new RO and send them over.

-- Backup rw
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'D:\data\TestDB_RW3.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- backup new RO

BACKUP DATABASE [TestDB]
FILEGROUP = N'TestDB_Part3'
TO  DISK = N'D:\data\TestDB_RO_Part3.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



We start our restore w/ the RW and expect the verification to fail bc it is missing filegroups.

--restore RW
use master
exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW3.bak'  WITH  REPLACE,  PARTIAL, FILE = 1, 
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  NOUNLOAD,  STATS = 10
GO
--- Verify (should fail)

use testdb2

SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')

Restore your 'new' RO file and you may expect this to be enough (but you would be wrong)



-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM
DISK = N'D:\data\TestDB_RO_Part3.bak'   WITH 
MOVE 'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf'

--- Verify (should fail)
use testdb2
SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')

If you notice, Partition 2 is still recovery pending.  But wasn't it there already?  Well, yes, you just have to tell SQL server it is in a good state.   Note that you could (if you wanted to waste IO) restore partition 2 from backup instead.
-- bring FG 2 online
RESTORE DATABASE [TestDB2] FILEGROUP='TestDB_Part2'
 WITH RECOVERY
--- Verify (should succeed)

use testdb2

SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')

One more runthrough of setting a FG RO and restoring it.
-- set FG 4 to read only

use master
exec master.dbo.usp_killconnections [TestDB]
ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part4] READONLY
GO

-- Backup rw
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'D:\data\TestDB_RW4.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- backup new RO

BACKUP DATABASE [TestDB]
FILEGROUP = N'TestDB_Part4'
TO  DISK = N'D:\data\TestDB_RO_Part4.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



--restore RW
use master
exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW4.bak'  WITH  PARTIAL, FILE = 1, 
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO
--- Verify (should fail)

use testdb2

SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')

-- bring FG 2, 3 online
use master
RESTORE DATABASE [TestDB2]
FILEGROUP='TestDB_Part2',
FILEGROUP='TestDB_Part3'
 WITH RECOVERY

--- Verify (should fail)

use testdb2
select * from sys.master_files  where database_id = 13

use testdb2

SELECT *
FROM TestTable;
GO
-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part4'  FROM
DISK = N'D:\data\TestDB_RO_Part4.bak'   WITH 
MOVE 'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf'


--- Verify (should succeed)
use testdb2

SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')


And finally, a quick run through of a complete restore from scratch.  Note that this uses the excellent usp_killconnections from here.



-- disaster recovery
use master
exec master.dbo.usp_killconnections [TestDB2]
drop database testdb2

use master
exec master.dbo.usp_killconnections [TestDB2]

-- restore RW

RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW4.bak'  WITH  PARTIAL, FILE = 1, 
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO

-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM
DISK = N'D:\data\TestDB_RO_Part2.bak'   WITH 
MOVE 'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf'


-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM
DISK = N'D:\data\TestDB_RO_Part3.bak'   WITH 
MOVE 'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf'

-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part4'  FROM
DISK = N'D:\data\TestDB_RO_Part4.bak'   WITH 
MOVE 'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf'


--- Verify (should succeed)
use testdb2

SELECT *
FROM TestTable;
GO
-- check filegroups.  especially state_desc
select * from sys.master_files 
where database_id = (select database_id from sys.databases where name = 'TestDB2')



Good luck!



2 comments:

  1. This was a huge help! I walked through the entire post. Thank you for all that work!

    Just a couple of minor corrections:
    #1:
    Near where you have, "If you notice, Partition 2 is still recovery pending"
    add:
    use master

    #2:
    Replace "select * from sys.master_files where database_id = 13"
    with:
    select * from sys.master_files where database_id=(
    select database_id from sys.databases where name='TestDB2'
    )

    ReplyDelete
  2. This is working fine, but i have a special requirement. Can you please help me to do ?

    We have to remove the Part2 and Part3 filegroups on TestDB. Basically removing partition and remove the files and filegroup.

    And restore the part4 and part5 on the TestDB2.

    ReplyDelete

analytics