/*============================================================================
  File:     3_FullTlog_Hack.sql

  Summary:  This script shows how to take full and 
			transaction log backups.

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

/*
	recreate AW2016_Hack
*/

/*
	Set recovery to FULL 
*/
ALTER DATABASE [AdventureWorks2016_Hack] SET RECOVERY FULL;
GO


/*
	Backup the database
	exclude: SKIP, NOREWIND, NOUNLOAD
	use INIT
*/
BACKUP DATABASE [AdventureWorks2016_Hack] 
	TO  DISK = N'C:\Backups\AW2016_Hack_BaseFull.bak' 
	WITH NOFORMAT, 
	INIT,   
	COMPRESSION,  
	STATS = 10;
GO


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

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



/*
	Now that we have a full backup, we can take transaction log backups
	But first, check out log usage
*/
DBCC LOGINFO('AdventureWorks2016_Hack');
GO
--note number of VLFs:



/*
	Modify some data and then check VLFs
*/
USE [AdventureWorks2016_Hack];
GO

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

DBCC LOGINFO('AdventureWorks2016_Hack');
GO

UPDATE [Person].[Person]
SET [LastName] = 'Stark'
WHERE [FirstName] like 'T%';
GO

DBCC LOGINFO('AdventureWorks2016_Hack');
GO


/*
	What does the UI give us if we script out the
	transaction log backup?
*/
BACKUP LOG [AdventureWorks2016_Hack] 
	TO  DISK = N'C:\Backups\AW2016_Hack_TLog1.trn' 
	WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2016_Hack-Transaction Log  Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10;
GO


DBCC LOGINFO('AdventureWorks2016_Hack');
GO

/*
	Modify more data
*/
USE [AdventureWorks2016_Hack];
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

DBCC LOGINFO('AdventureWorks2016_Hack');
GO


/*
	Slightly different syntax
	Removed SKIP, NOREWIND, NOUNLOAD, NOFORMAT
*/
BACKUP LOG [AdventureWorks2016_Hack] 
	TO  DISK = N'C:\Backups\AW2016_Hack_TLog2.trn'
	WITH INIT,  
	STATS = 10;
GO

DBCC LOGINFO('AdventureWorks2016_Hack');
GO


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

DBCC LOGINFO('AdventureWorks2016_Hack');
GO


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


DBCC LOGINFO('AdventureWorks2016_Hack');
GO


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

DBCC LOGINFO('AdventureWorks2016_Hack');
GO


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

USE [AdventureWorks2016_Hack];
GO

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


/*
	Modify more data, within a transaction
	DO NOT commit the transaction
*/
BEGIN TRANSACTION

UPDATE [Person].[Person]
SET [MiddleName] = 'aka',[LastName] = 'Wolverine'
WHERE [FirstName] = 'Logan';
GO


/*
	execute a CHECKPOINT
*/
CHECKPOINT;
GO


/*
	Before we cause a disaster, check our data...
*/
SELECT [FirstName], [LastName]
FROM [Person].[Person]
WHERE [FirstName] = 'Bruce';
GO


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT [FirstName], [MiddleName], [LastName]
FROM [Person].[Person]
WHERE [FirstName] = 'Logan';
GO


/*
	Shutdown the server with NOWAIT to simulate a power failure or 
	other horrible disaster you really hope never happens

	This MUST be completed in another window as SHUTDOWN cannot
	be part of a transaction (disconnect this connection)

	Here's the exact command to copy/paste:
	SHUTDOWN WITH NOWAIT
*/


/*
	While SQL Server is shutdown, open Command Prompt as admin
	change to the data directory, then delete the data file:
	cd C:\Databases\AdventureWorks2016_Hack
	del AdventureWorks2016_Hack_Data.mdf
	
	Once the data file is gone, restart SQL Server:
	net start mssql$SQL2014

*/

/*
	After restart, check the databases
	AdventureWorks2016 hasn't recovered
	check it another way
*/
EXEC sp_helpdb;
GO

SELECT 
	[database_id],
	[name], 
	[state_desc]
FROM [sys].[databases]
ORDER BY [database_id];
GO

/*
	Review Error Log for recovery errors

	Error: 17204, Severity: 16, State: 1
	FCB::Open failed: Could not open file C:\Databases\SQL2014\AdventureWorks2016\AdventureWorks2016_Data.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
	Error: 5120, Severity: 16, State: 101
	Unable to open the physical file "C:\Databases\SQL2014\AdventureWorks2016\AdventureWorks2016_Data.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
	
*/

/*
	Can we get in?
*/
USE [AdventureWorks2016_Hack];
GO

/*
	First response...panic
	Second response...take the database offline
*/
ALTER DATABASE [AdventureWorks2016_Hack] SET OFFLINE;
GO

/*
	Oh no...bring it online...
*/
ALTER DATABASE [AdventureWorks2016_Hack] SET ONLINE;
GO


/*
	detach...does not work
*/
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2016_Hack'
GO

/*
	drop the database...log is still there
	recreate the Hack database
*/

ALTER DATABASE [AdventureWorks2016_Hack] SET OFFLINE;
GO


/*
	bring it online
*/
ALTER DATABASE [AdventureWorks2016_Hack] SET ONLINE;
GO

/*
	Now can you backup the TAIL of the log?
	Note: you won't have the GUI available here
*/

BACKUP LOG [AdventureWorks2016_Hack]
	TO DISK = N'C:\Backups\SQL2014\AW2016_Hack_TailLog.trn'
	WITH NO_TRUNCATE,-- this is the option that allows you to backup an "orphaned transaction log"
	STATS = 10;
GO

RESTORE HEADERONLY 
FROM DISK = N'C:\Backups\SQL2014\AW2016_Hack_TailLog.trn';
GO


/*
	You can recover from this!
	(Demo later)
*/



/*

*/