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

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

  Date:     April 2015

  SQL Server Version: 2005/2008/2008R2/2012/2014
------------------------------------------------------------------------------
  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 AW2014_Hack
*/

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


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


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

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


/*
	Modify some data 
*/
USE [AdventureWorks2014_Hack];
GO

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

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


/*
	transaction log backup
*/
BACKUP LOG [AdventureWorks2014_Hack] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_Hack_TLog1.trn' 
	WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014_Hack-Transaction Log  Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10;
GO


/*
	Modify more data
*/
USE [AdventureWorks2014_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



/*
	backup log
*/
BACKUP LOG [AdventureWorks2014_Hack] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_Hack_TLog2.trn'
	WITH INIT,  
	STATS = 10;
GO

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

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

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

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


/*
	Modify some data
*/
USE [AdventureWorks2014_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;
GO
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\AdventureWorks2014_Hack
	del AdventureWorks2014_Hack_Log.ldf
	
	Once the data file is gone, restart SQL Server:
	net start mssql$SQL2014

*/

/*
	After restart, check the databases
	AdventureWorks2014 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: 17207, Severity: 16, State: 1.
	FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Databases\AdventureWorks2014_Hack\AdventureWorks2014_Hack_Log.ldf'. Diagnose and correct the operating system error, and retry the operation.
	File activation failure. The physical file name "C:\Databases\AdventureWorks2014_Hack\AdventureWorks2014_Hack_Log.ldf" may be incorrect.
	The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

*/

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

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

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


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

/*
	Can't backup the tail of the log
*/
BACKUP LOG [AdventureWorks2014_Hack]
	TO DISK = N'C:\Backups\SQL2014\AW2014_Hack_TailLog.trn'
	WITH NO_TRUNCATE,-- this is the option that allows you to backup an "orphaned transaction log"
	STATS = 10;
GO


/*
	current status of database
*/
SELECT [state_desc]
FROM [sys].[databases]
WHERE [name] = N'AdventureWorks2014_Hack';
GO

/*
	try running to CHECKDB and repair
	note: what data is lost will not be known
	backup first!
*/
BACKUP DATABASE [AdventureWorks2014_Hack]
	TO  DISK = N'C:\Backups\SQL2014\AW2014_Hack_BeforeRepair.bak' 
	WITH NOFORMAT, 
	INIT,   
	COMPRESSION,  
	STATS = 10;
GO

DBCC CHECKDB (N'AdventureWorks2014_Hack', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO


/*
	needs to be in emergency mode, won't run in Recovery_Pending
*/
ALTER DATABASE [AdventureWorks2014_Hack] SET EMERGENCY;
GO

DBCC CHECKDB (N'AdventureWorks2014_Hack', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

/*
	argh, need single-user mode to do a repair with data loss
*/
ALTER DATABASE [AdventureWorks2014_Hack] SET SINGLE_USER;
GO
DBCC CHECKDB (N'AdventureWorks2014_Hack', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

/*
	READ THE ERROR :)
	also, check the output in the ERRORLOG

	Check the status of the database
*/	
SELECT [state_desc]
FROM [sys].[databases]
WHERE [name] = N'AdventureWorks2014_Hack';
GO


/*
	set it to multi-user, check the data
*/
ALTER DATABASE [AdventureWorks2014_Hack] SET MULTI_USER;
GO

USE [AdventureWorks2014_Hack];
GO

SELECT [FirstName], [LastName]
FROM [Person].[Person]
WHERE [LastName] = 'Banner';
GO

SELECT [FirstName], [MiddleName], [LastName]
FROM [Person].[Person]
WHERE [FirstName] = 'Logan';
GO

/*
	Even though the second transaction was NOT committed
	We ran checkpoint, changes were flushed to disk
	No log available to undo the operation
*/