/*============================================================================
  File:     7a_Restoring_Full_Tlog_Backups.sql

  Summary:  This script creates a full back and transaction log
			backups to use for restore testing.

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


/*
	Verify the recovery model
*/ 
USE [master];
GO

SELECT DATABASEPROPERTYEX('AdventureWorks2016_COPY', 'Recovery');
GO

SELECT [name], [recovery_model_desc]
FROM [sys].[databases]
WHERE [name] = 'AdventureWorks2016_COPY';
GO

/*
	Set recovery to FULL if needed
*/
ALTER DATABASE [AdventureWorks2016_COPY] SET RECOVERY FULL;
GO


/* 
	Take the initial backup
*/
BACKUP DATABASE [AdventureWorks2016_COPY] 
	TO  DISK = N'C:\Backups\AW2016_COPY_InitialFull.bak' 
	WITH NOFORMAT, 
	INIT,   
	COMPRESSION,  
	STATS = 10;
GO


RESTORE HEADERONLY 
FROM DISK = N'C:\Backups\AW2016_COPY_InitialFull.bak';
GO

RESTORE FILELISTONLY
FROM DISK = N'C:\Backups\AW2016_COPY_InitialFull.bak';
GO



/*
	Modify some data and then take a transaction log backup
*/
USE [AdventureWorks2016_COPY];
GO

UPDATE [Person].[Person]
SET [LastName] = 'Stark'
WHERE [FirstName] = 'Tony';
GO

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


/*
	Modify more data
*/
USE [AdventureWorks2016_COPY];
GO

UPDATE [Person].[Person]
SET [LastName] = 'Wayne'
WHERE [FirstName] like 'Bruce';
GO

UPDATE [Person].[Person]
SET [LastName] = 'Howlett'
WHERE [FirstName] like 'James';
GO

UPDATE [Person].[Person]
SET [LastName] = 'Xavier'
WHERE [FirstName] like 'Charles';
GO

UPDATE [Person].[Person]
SET [LastName] = 'Bristow'
WHERE [FirstName] like 'Sydney';
GO

UPDATE [Person].[Person]
SET [Title] = 'Ms.'
WHERE [LastName] = 'Bristow';
GO

UPDATE [Person].[Person]
SET [Title] = 'Mr.'
WHERE [LastName] IN ('Stark', 'Wayne', 'Howlett', 'Xavier');
GO

UPDATE [Person].[Person]
SET [ModifiedDate] = GETDATE()
WHERE [LastName] IN ('Stark', 'Wayne', 'Howlett', 'Bristow', 'Xavier');
GO


/*
	Take another backup
*/
BACKUP LOG [AdventureWorks2016_COPY] 
	TO  DISK = N'C:\Backups\AW2016_COPY_TLog2.trn'
	WITH INIT,  
	STATS = 10;
GO


UPDATE [Person].[Person]
SET [ModifiedDate] = GETDATE()
WHERE [PersonType] = 'SC' ;
GO

/*
	Note the time...
*/
SELECT SYSDATETIME();
-- <insert here>  


/*
	Uh-oh...someone makes a mistake
*/
DELETE FROM [Sales].[SalesOrderDetail]
WHERE [LineTotal] > 10000.00;
GO


/*
	But you're not notified right away...
*/

UPDATE [Person].[Person]
SET [ModifiedDate] = GETDATE()
WHERE [PersonType] <> 'IN';
GO

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

/*
	NOW you're notified...
	What do you do?
*/


/*
	Some options:
	1: Restore last full backup and apply TL1 and TL2.  Lose everything after TL2.
	2: Restore last full backup and apply TL1 and TL2, and incrementally apply TL3...
	3: Restore last full backup to another location (or with a different name) and apply TL1 and TL2, and re-insert the data that was deleted

	What are the factors you have to consider here?
*/
