/*============================================================================
  File:     6c_Restoring_Full_TLog_Backups_UsingStandby.sql

  Summary:  This script shows how to restore to a point in time in
			the transaction log backup using a standby file.

  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.
============================================================================*/

IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = N'AdventureWorks2016_TEST')
	BEGIN
		DROP DATABASE [AdventureWorks2016_TEST]
	END

/*
	STOPAT time format:
	N'2016-04-01T09:30:44'
*/
USE [master];
GO
RESTORE DATABASE [AdventureWorks2016_TEST] 
	FROM  DISK = N'C:\Backups\AW2016_COPY_InitialFull.bak' WITH  FILE = 1,  
	MOVE N'AdventureWorks2016_COPY_Data' TO N'C:\Databases\AW_TEST\AdventureWorks2016_TEST_Data.mdf',  
	MOVE N'AdventureWorks2016_COPY_Log' TO N'C:\Databases\AW_TEST\AdventureWorks2016_TEST_Log.ldf',  
	NORECOVERY,  NOUNLOAD,  STATS = 5;
GO

RESTORE LOG [AdventureWorks2016_TEST] 
	FROM  DISK = N'C:\Backups\AW2016_COPY_TLog1.trn' WITH  FILE = 1,  
	NORECOVERY,  NOUNLOAD,  STATS = 5;
GO

RESTORE LOG [AdventureWorks2016_TEST] 
	FROM  DISK = N'C:\Backups\AW2016_COPY_TLog2.trn' WITH  FILE = 1,  
	NORECOVERY,  NOUNLOAD,  STATS = 5;
GO

RESTORE LOG [AdventureWorks2016_TEST] 
	FROM  DISK = N'C:\Backups\AW2016_COPY_TLog3.trn' WITH  FILE = 1,  
	STANDBY = N'C:\Backups\AdventureWorks2016_COPY_RollbackUndo_2013-10-01_1.bak',  
	NOUNLOAD,  STATS = 5,  STOPAT = N'<INSERTTIMEHERE>';
GO

/*
	check to see if last change before delete is in the database
	and verify delete hasn't been run
*/
USE [AdventureWorks2016_TEST];
GO

SELECT * 
FROM [Person].[Person]
WHERE [PersonType] = 'SC';
GO

SELECT COUNT(*)
FROM [Sales].[SalesOrderDetail]
WHERE [LineTotal] > 10000.00;
GO

/*
	it's not, roll forward
*/
USE [master];
GO

RESTORE LOG [AdventureWorks2016_TEST] 
	FROM  DISK = N'C:\Backups\AW2016_COPY_TLog3.trn' WITH  FILE = 1,  
	STANDBY = N'C:\Backups\AdventureWorks2016_COPY_RollbackUndo_2013-10-01_1.bak',  
	NOUNLOAD,  STATS = 5,  STOPAT = N'<INSERTTIMEHERE>';
GO

/*
	check again
*/
USE [AdventureWorks2016_TEST];
GO

SELECT * 
FROM [Person].[Person]
WHERE [PersonType] = 'SC' ;
GO

SELECT COUNT(*)
FROM [Sales].[SalesOrderDetail]
WHERE [LineTotal] > 10000.00;
GO

/*
	it's still not, roll forward again
*/
USE [master];
GO

RESTORE LOG [AdventureWorks2016_TEST] 
	FROM  DISK = N'C:\Backups\AW2016_COPY_TLog3.trn' WITH  FILE = 1,  
	STANDBY = N'C:\Backups\AdventureWorks2016_COPY_RollbackUndo_2013-10-01_1.bak',  
	NOUNLOAD,  STATS = 5,  STOPAT = N'<INSERTTIMEHERE>';
GO


/*
	check again
*/
USE [AdventureWorks2016_TEST];
GO

SELECT * 
FROM [Person].[Person]
WHERE [PersonType] = 'SC' ;
GO

SELECT COUNT(*)
FROM [Sales].[SalesOrderDetail]
WHERE [LineTotal] > 10000.00;
GO

/*
	It's there!
	You can bring the database online...
	or push a little farther...
*/	
USE [master];
GO

RESTORE LOG [AdventureWorks2016_TEST] 
	FROM  DISK = N'C:\Backups\AW2016_COPY_TLog3.trn' WITH  FILE = 1,  
	STANDBY = N'C:\Backups\AdventureWorks2016_COPY_RollbackUndo_2013-10-01_1.bak',  
	NOUNLOAD,  STATS = 5,  STOPAT = N'<INSERTTIMEHERE>';
GO


/*
	fingers crossed...
*/
USE [AdventureWorks2016_TEST];
GO

SELECT COUNT(*)
FROM [Sales].[SalesOrderDetail]
WHERE [LineTotal] > 10000.00;
GO


/*
	bring the database online
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2016_TEST] WITH RECOVERY;
GO

