Wednesday, May 30, 2012

Differential and Partial Differential backups with Partitioned tables and Read only FileGroups


Here is a quick write up w/ little commentary.  This is a corollary to an earlier article.  I wanted to see how differential backups are affected by partitioning and read only.  See the comments to see where we are.  If I have time in the future I will try to hash it out but right now it is get it up as this or don’t get it online at all.  Hope this helps someone.

The idea isn’t to run this whole block of code.   Run it chunk by chunk and see what happens. Standard disclaimers, if you don’t know what this is doing, it isn’t my problem if you bork your system.

This was all done on SQL 2008R2.

--Setup test dbs to use for walkthrough
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='c:\data\TestDB_Part1.mdf',
SIZE=3, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2', FILENAME ='c:\data\TestDB_Part2.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part3
(NAME = 'TestDB_Part3', FILENAME ='c:\data\TestDB_Part3.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part4
(NAME = 'TestDB_Part4', FILENAME ='c:\data\TestDB_Part4.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part5
(NAME = 'TestDB_Part5', FILENAME ='c:\data\TestDB_Part5.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );
GO


USE TestDB;
GO
--- Step 2 : Create Partition Range Function
CREATE 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

-- set FG 2 to read only
ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part2] READONLY
GO


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

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

-- add data
use testdb

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4
VALUES (24,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (44,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (54,GETDATE());

-- Backup DIFF
BACKUP DATABASE [TestDB]
TO  DISK = N'c:\data\TestDB_DIFF.bak'
WITH NOFORMAT, INIT, DIFFERENTIAL  ,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--backup Partial (RW) diff
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'c:\data\TestDB_DIFFPartial.bak'
WITH NOFORMAT, INIT, DIFFERENTIAL  ,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- list backup files.  We did not backup the read only file group in either diff
RESTORE FILELISTONLY from disk =  N'c:\data\TestDB_Diff.bak'
RESTORE FILELISTONLY from disk =  N'c:\data\TestDB_DiffPartial.bak'

--restore RW portion w/ no recovery
use master
exec master.dbo.usp_killconnections [TestDB2]
drop database testdb2

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


-- see files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


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

--hmm, some warnings:
----The roll forward start point is now at log sequence number (LSN) 20000000057700001. Additional roll forward past LSN 20000000061400001 is required to complete the restore sequence.
----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

-- All files in restoring now...
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


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

-- All files online now...
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--let's try w/ diff before the ro
--restore RW portion w/ no recovery
use master
exec master.dbo.usp_killconnections [TestDB2]
drop database testdb2

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


-- see files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore partial diff and bring online
RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'c:\data\TestDB_DIFFPartial.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10
GO
-- still some warnings
----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.
----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

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

-- All files online now...
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- ok, for completeness, what is the difference if I use the 'full' differential?  (ie the non partial one)
--restore RW portion w/ no recovery
use master
exec master.dbo.usp_killconnections [TestDB2]
drop database testdb2

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


-- see files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore  diff and bring online
RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'c:\data\TestDB_DIFF.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO
--hmmm got some errors
----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.
----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

-- see files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

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

-- All files online now...
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--restore the RO first (before the diff)


--restore RW portion w/ no recovery
use master
exec master.dbo.usp_killconnections [TestDB2]
drop database testdb2

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


-- see files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM
DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 
MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'
--same roll forward error:
----The roll forward start point is now at log sequence number (LSN) 20000000057700001. Additional roll forward past LSN 20000000061400001 is required to complete the restore sequence.
----This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

-- see files in restoring
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--restore  diff and bring online
RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'c:\data\TestDB_DIFF.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO
-- see all files online
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--ok, doesn't seem to matter much as long as we do all of them.  Lets make a new file readonly





-- set FG 3 to read only
ALTER DATABASE [TestDB2] MODIFY FILEGROUP [TestDB_Part3] READONLY
GO


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

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

-- add data
use testdb2

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3 SHOULD FAIL due to read only
VALUES (25,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (35,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (55,GETDATE());

-- Backup DIFF
BACKUP DATABASE [TestDB2]
TO  DISK = N'c:\data\TestDB2_DIFF.bak'
WITH NOFORMAT, INIT, DIFFERENTIAL  ,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--backup Partial (RW) diff
BACKUP DATABASE [TestDB2]
READ_WRITE_FILEGROUPS
TO  DISK = N'c:\data\TestDB2_DIFFPartial.bak'
WITH NOFORMAT, INIT, DIFFERENTIAL  ,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- list backup files.  We did not backup the read only file group in either diff
RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_Diff.bak' --has both read only files
RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_DiffPartial.bak' --has neither read only file

--try restoring with no read only files (just main and big diff)

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

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

-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--restore  diff and bring online
RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'c:\data\TestDB2_DIFF.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO

--despite saying the file is there in the backup, we still get issues with RO files
----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.
----The file "TestDB_Part3" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM
DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 
MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'
--warning we are still missing 3 so cannot come online

use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM
DISK = N'c:\data\TestDB2_RO_Part3.bak'   WITH 
MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'
-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--try restoring with partial diff
use master
exec master.dbo.usp_killconnections [TestDB2]
drop database testdb2

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

-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--restore  diff and bring online
RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'c:\data\TestDB2_DIFFPartial.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO

--same issues with RO files
----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.
----The file "TestDB_Part3" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


-- restore RO
use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM
DISK = N'c:\data\TestDB_RO_Part2.bak'   WITH 
MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'
--warning we are still missing 3 so cannot come online

use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM
DISK = N'c:\data\TestDB2_RO_Part3.bak'   WITH 
MOVE 'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf'
-- see all files online
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--it seems we cannot come online until all full, diffs and RO files are restored.  This is different
--  than when we have just a partial backup and RO (no diffs), where we can come online after just the partial backup

--one more scenario.  what if i make something read only and then take a diff backup before a 'full' partial (i should really look up the correct terminology)


-- set FG 4 to read only
ALTER DATABASE [TestDB2] MODIFY FILEGROUP [TestDB_Part4] READONLY
GO


-- add data
use testdb2

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4, SHOULD FAIL due to read only
VALUES (35,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (55,GETDATE());


--backup RO
BACKUP DATABASE [TestDB2]
FILEGROUP = N'TestDB_Part4'
TO  DISK = N'c:\data\TestDB2_RO_Part4.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


-- Backup DIFF
BACKUP DATABASE [TestDB2]
TO  DISK = N'c:\data\TestDB2_DIFF2.bak'
WITH NOFORMAT, INIT, DIFFERENTIAL  ,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--backup Partial (RW) diff
BACKUP DATABASE [TestDB2]
READ_WRITE_FILEGROUPS
TO  DISK = N'c:\data\TestDB2_DIFFPartial2.bak'
WITH NOFORMAT, INIT, DIFFERENTIAL  ,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- list backup files.  We did not backup the read only file group in either diff
RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_Diff2.bak' --has all read only files
RESTORE FILELISTONLY from disk =  N'c:\data\TestDB2_DiffPartial2.bak' --has none of the read only files

--restore test
use master
exec master.dbo.usp_killconnections [TestDB2]
drop database testdb2

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

-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--restore  diff and bring online
RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'c:\data\TestDB2_DIFF2.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO

--same issues with RO files
----The file "TestDB_Part2" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.
----The file "TestDB_Part3" was not fully restored by a database or file restore. The entire file must be successfully restored before applying this backup set.

-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


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

use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM
DISK = N'c:\data\TestDB2_RO_Part3.bak'   WITH 
MOVE 'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf'

use master
RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part4'  FROM
DISK = N'c:\data\TestDB2_RO_Part4.bak'   WITH 
MOVE 'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf'

-- see all files online
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')

--so this all works about how we would expect, except that you cannot come online after just a base backup and
-- a partial diff restore.  (you must get your RO online as well before the db will come up).  note that if you are
-- restoring in place and your Read only hasn't changed since your base backup,  your RO will be found as in:

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

-- see all files offline
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


--restore  diff and bring online
RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'c:\data\TestDB2_DIFF.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'c:\data\TestDB2_Part1.mdf', 
      MOVE N'TestDB_Part2' TO N'c:\data\TestDB2_Part2.mdf', 
      MOVE N'TestDB_Part3' TO N'c:\data\TestDB2_Part3.mdf', 
      MOVE N'TestDB_Part4' TO N'c:\data\TestDB2_Part4.mdf', 
      MOVE N'TestDB_Part5' TO N'c:\data\TestDB2_Part5.mdf', 
      MOVE N'TestDB_log' TO N'c:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10, recovery
GO
-- see all files online
select * from sys.master_files  where database_id = (select database_id from sys.databases where name = 'testdb2')


analytics