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

  Summary:  This script shows how to perform a
			partial restore from scratch

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by SQLskills.com

  (c) SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  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 AW2014_Filegroups
*/
USE [AdventureWorks2014_Filegroups];
GO

SELECT *
FROM [sys].[filegroups];

SELECT *
FROM [sys].[database_files];

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

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

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

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

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

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

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


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

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

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


/*
	Move some tables into the filesgroups
	TransactionHistoryArchive to ArchiveData
*/
USE [AdventureWorks2014_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
	)
	ON [NewData];
GO


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


CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderHeader_rowguid] 
	ON [Sales].[SalesOrderHeader]
	(
		[rowguid] ASC
	)
	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 [AdventureWorks2014_Filegroups] SET RECOVERY FULL WITH NO_WAIT;
GO

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

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

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

DROP DATABASE [AdventureWorks2014_Filegroups];
GO


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


/*
	Start with the PRIMARY filegroup
*/
RESTORE DATABASE [AdventureWorks2014_Filegroups]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Backups\SQL2014\AW2014_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 [AdventureWorks2014_Filegroups]
	FILEGROUP = 'NewData'
	FROM DISK = 'C:\Backups\SQL2014\AW2014_Filegroups_Full.bak'
	WITH NORECOVERY, 
	STATS;
GO

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

/*
	Restore additional tlog backups as needed
*/

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

/*
	Query the data
*/
USE [AdventureWorks2014_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
	[AdventureWorks2014_Filegroups].[sys].[database_files];
GO

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

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

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

RESTORE DATABASE [AdventureWorks2014_Filegroups] WITH RECOVERY;
GO


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

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

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

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

SELECT *
FROM [Production].[TransactionHistoryArchive]
GO