/*============================================================================
  File:     8_Partial_Restore.sql

  Summary:  This script shows how to perform a partial restore.

  Date:     April 2017

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  For more scripts and other useful content, go to:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Erin Stellato.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE [master];
GO

/*
	View filegroups for AW2016_Filegroups
*/
USE [AdventureWorks2016_Filegroups];
GO

SELECT *
FROM [sys].[filegroups];

SELECT *
FROM [sys].[database_files];

/*
	Add a new filegroup
*/
USE [master];
GO
ALTER DATABASE [AdventureWorks2016_Filegroups] 
	ADD FILEGROUP [NewData];
GO

/*
	Add some files, then set as default
*/
USE [master]
GO
ALTER DATABASE [AdventureWorks2016_Filegroups] 
	ADD FILE ( NAME = N'AdventureWorks_2012_Filegroups_NewData_1', 
		FILENAME = N'C:\Databases\AdventureWorks2016_Filegroups\AdventureWorks_2012_Filegroups_NewData_1.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [NewData];

ALTER DATABASE [AdventureWorks2016_Filegroups] 
	ADD FILE ( NAME = N'AdventureWorks_2012_Filegroups_NewData_2', 
		FILENAME = N'C:\Databases\AdventureWorks2016_Filegroups\AdventureWorks_2012_Filegroups_NewData_2.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [NewData];
GO

ALTER DATABASE [AdventureWorks2016_Filegroups] 
	MODIFY FILEGROUP [NewData] DEFAULT;
GO

/*
	Add another filegroup and some files
*/
USE [master];
GO
ALTER DATABASE [AdventureWorks2016_Filegroups] 
	ADD FILEGROUP [ArchiveData];
GO

ALTER DATABASE [AdventureWorks2016_Filegroups] 
	ADD FILE ( NAME = N'AdventureWorks_2012_Filegroups_ArchiveData_1', 
		FILENAME = N'C:\Databases\AdventureWorks2016\AdventureWorks_2012_Filegroups_ArchiveData_1.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [ArchiveData];

ALTER DATABASE [AdventureWorks2016_Filegroups] 
	ADD FILE ( NAME = N'AdventureWorks_2012_Filegroups_ArchiveData_2', 
		FILENAME = N'C:\Databases\AdventureWorks2016\AdventureWorks_2012_Filegroups_ArchiveData_2.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [ArchiveData];
GO


/*
	revisit existing files and filegroups
*/
USE [AdventureWorks2016_Filegroups];
GO

SELECT *
FROM [sys].[filegroups];
GO

SELECT *
FROM [sys].[database_files];
GO


/*
	Move some tables into the filesgroups
	TransactionHistoryArchive to ArchiveData
*/
USE [AdventureWorks2016_Filegroups];
GO

CREATE UNIQUE CLUSTERED INDEX [PK_TransactionHistoryArchive_TransactionID]
	ON [Production].[TransactionHistoryArchive] (
		[TransactionID] ASC
		)
	WITH DROP_EXISTING
ON [ArchiveData];
GO

CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID] 
	ON [Production].[TransactionHistoryArchive] (
		[ReferenceOrderID] ASC,
		[ReferenceOrderLineID] ASC
		)
	WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF)
	ON [ArchiveData];
GO

CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_ProductID] 
	ON [Production].[TransactionHistoryArchive] (
		[ProductID] ASC
		)
	WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF)
	ON [ArchiveData];
GO

CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_TransactionDate_TransactionType] 
	ON [Production].[TransactionHistoryArchive] (
		[TransactionDate] ASC,
		[TransactionType] ASC
		);

/*
	SalesOrderHeader and SalesOrderDetail data to NewData FG
*/
ALTER TABLE [Sales].[SalesOrderHeaderSalesReason]
	DROP CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID];
GO

ALTER TABLE [Sales].[SalesOrderDetail]
	DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID];
GO

ALTER TABLE [Sales].[SalesOrderHeader] 
	DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID];
GO

ALTER TABLE [Sales].[SalesOrderHeader] 
	ADD  CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] 
	PRIMARY KEY CLUSTERED 
	(
		[SalesOrderID] ASC
	)
	ON [NewData];
GO

CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_SalesPersonID] 
	ON [Sales].[SalesOrderHeader]
	(
		[SalesPersonID] ASC
	)
	ON [NewData];
GO

CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_CustomerID] 
	ON [Sales].[SalesOrderHeader]
	(
		[CustomerID] ASC
	)
	WITH DROP_EXISTING
	ON [NewData]
	;
GO


CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderHeader_SalesOrderNumber] 
	ON [Sales].[SalesOrderHeader]
	(
		[SalesOrderNumber] ASC
	)
	WITH DROP_EXISTING
	ON [NewData];
GO


CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderHeader_rowguid] 
	ON [Sales].[SalesOrderHeader]
	(
		[rowguid] ASC
	)
	WITH DROP_EXISTING
	ON [NewData];
GO


CREATE UNIQUE CLUSTERED INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
    ON [Sales].[SalesOrderDetail](
		[SalesOrderID] ASC,
		[SalesOrderDetailID] ASC
	)
	WITH (DROP_EXISTING =  ON )
	ON [NewData];


CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID]
    ON [Sales].[SalesOrderDetail](
		[ProductID] ASC
	)
	WITH (DROP_EXISTING =  ON )
	ON [NewData];


CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderDetail_rowguid
    ON [Sales].[SalesOrderDetail](
		[rowguid] ASC
	)
	WITH (DROP_EXISTING =  ON )
	ON [NewData];

/*
	check index location
*/
SELECT [o].[name], [i].[name], [i].[type_desc], [fg].[name]
FROM [sys].[indexes] [i]
JOIN [sys].[objects] [o] ON [i].[object_id] = [o].[object_id]
JOIN [sys].[filegroups] [fg] ON [i].[data_space_id] = [fg].[data_space_id]
WHERE [o].[type] = 'U'
ORDER BY [fg].[name];
GO

/*
	set to full recovery
*/
USE [master]
GO
ALTER DATABASE [AdventureWorks2016_Filegroups] SET RECOVERY FULL WITH NO_WAIT;
GO

/*
	Make sure we have backups from which to restore
*/
BACKUP DATABASE [AdventureWorks2016_Filegroups] TO
	DISK = N'C:\Backups\AW2016_Filegroups_Full.bak'
	WITH INIT, STATS = 10;
GO

BACKUP LOG [AdventureWorks2016_Filegroups] TO
	DISK = N'C:\Backups\AW2016_Filegroups_Log1.trn'
	WITH INIT, STATS = 10;
GO

/*
	Oh no, a disaster!
*/
USE [master];
GO

DROP DATABASE [AdventureWorks2016_Filegroups];
GO


/*
	How to restore as fast as possible?
*/


/*
	Start with the PRIMARY filegroup
*/
RESTORE DATABASE [AdventureWorks2016_Filegroups]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Backups\AW2016_Filegroups_Full.bak'
	WITH PARTIAL, 
	NORECOVERY, 
	STATS;
GO


/*
	Cannot query the data yet!
*/
SELECT *
FROM [Production].[Product];
GO

SELECT *
FROM [Sales].[SalesOrderHeader];
GO

SELECT *
FROM [Sales].[SalesOrderDetail];
GO

SELECT *
FROM [Production].[TransactionHistoryArchive]
GO

/*
	Bring the active data (NewData) filegroup online
*/
RESTORE DATABASE [AdventureWorks2016_Filegroups]
	FILEGROUP = 'NewData'
	FROM DISK = 'C:\Backups\AW2016_Filegroups_Full.bak'
	WITH NORECOVERY, 
	STATS;
GO

/*
	Restore transaction log backups
*/
RESTORE LOG [AdventureWorks2016_Filegroups]
	FROM DISK = N'C:\Backups\AW2016_Filegroups_Log1.trn'
	WITH NORECOVERY, 
	STATS;
GO

/*
	Restore additional tlog backups as needed
*/

/*
	Bring the database online
*/
RESTORE DATABASE [AdventureWorks2016_Filegroups] WITH RECOVERY;
GO

/*
	Query the data
*/
USE [AdventureWorks2016_Filegroups];
GO

SELECT *
FROM [Production].[Product];
GO

SELECT *
FROM [Sales].[SalesOrderHeader];
GO

SELECT *
FROM [Sales].[SalesOrderDetail];
GO

SELECT *
FROM [Production].[TransactionHistoryArchive]
GO


/*
	Online piecemeal restore other filegroups at our leisure
*/
SELECT [name], [state_desc] FROM
	[AdventureWorks2016_Filegroups].[sys].[database_files];
GO

/*
	Bring the archive data (ArchiveData) filegroup online
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2016_Filegroups]
	FILEGROUP = 'ArchiveData'
	FROM DISK = 'C:\Backups\AW2016_Filegroups_Full.bak'
	WITH NORECOVERY, 
	STATS;
GO

/*
	Restore transaction log backups
*/
RESTORE LOG [AdventureWorks2016_Filegroups]
	FROM DISK = N'C:\Backups\AW2016_Filegroups_Log1.trn'
	WITH NORECOVERY, 
	STATS;
GO

RESTORE DATABASE [AdventureWorks2016_Filegroups] WITH RECOVERY;
GO


/*
	Query the data
*/
USE [AdventureWorks2016_Filegroups];
GO

SELECT *
FROM [Production].[Product];
GO

SELECT *
FROM [Sales].[SalesOrderHeader];
GO

SELECT *
FROM [Sales].[SalesOrderDetail];
GO

SELECT *
FROM [Production].[TransactionHistoryArchive]
GO