/*============================================================================
  File:     LoggingRecovery.sql

  Summary:  This script shows how recovery can be completed with no data loss and 
			complete data integrity when the "tail" of the log is accessible.
			Additionally, it shows how EMERGENCY mode can be used as a LAST RESORT
			for accessing a damaged database.

  Date:     April 12, 2005

  SQL Server Version: 9.00.1116.08 (s/b IDW14)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

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

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  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.
============================================================================*/


-- For simplicity and SO that you can reproduce this example later, we
-- will use the pubs database!

-- First, create a BACKUP that you can restore back to:
BACKUP DATABASE AdventureWorks
	TO DISK = N'C:\SQLTemp\AdventureWorksOriginal.bak'
	WITH INIT

-- Use the UI to generate the syntax required to do a restore with move:
RESTORE DATABASE [AdventureWorksCopy] 
FROM  DISK = N'C:\SQLTemp\AdventureWorksOriginal.bak' 
WITH  FILE = 1,  
	MOVE N'AdventureWorks_Data' 
		TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksCopy_Data.mdf',  
	MOVE N'AdventureWorks_Log' 
		TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksCopy_Log.ldf',  
NOUNLOAD,  STATS = 10

-- Verify the recovery model of AdventureWorksCopy...
SELECT DATABASEPROPERTYEX('AdventureWorksCopy', 'Recovery')

-- You need to change the recovery model to full
ALTER DATABASE AdventureWorksCopy
	SET RECOVERY FULL
go

----------------------------------------------------------------------------------------------------------
--- First recovery scenario - SETUP
----------------------------------------------------------------------------------------------------------

-- Next, you'll need to begin your "recovery strategy setup" by performing
-- some form of starting backup. In this case, we'll choose a full backup. 
-- NOTE: File or filegroup backups are also possible as a starting point
-- for recovery.
BACKUP DATABASE AdventureWorksCopy
	TO DISK = N'C:\SQLTemp\AventureWorksCopy.bak'
	WITH INIT
go

-- Now we can begin to perform transactions as well as manage those transactions
-- through transaction log backups.

USE AdventureWorksCopy
go

-- For simplicity - we'll make changes to the Person.Contact table in order to 
-- setup the same set of transactions as in the slide: "Transaction Recovery & Checkpoints"

SELECT *
FROM Person.Contact AS C
WHERE ContactID < 10
go

-- This is transaction 1 from slide titled: "Transaction Recovery & Checkpoints"
UPDATE Person.Contact
	SET FirstName = 'T1' -- and this will commit b4 checkpoint
	WHERE ContactID = 1
go

-- This is transaction 3 from slide titled: "Transaction Recovery & Checkpoints"
BEGIN TRANSACTION
UPDATE Person.Contact
	SET FirstName = 'T3' -- and this will NOT commit b4 checkpoint
	WHERE ContactID = 3

-- go to another window (or here) and run a CHECKPOINT in this database

-- shutdown the server with nowait (power failure, etc.)
-- this MUST be completed in another window as SHUTDOWN cannot
-- be part of a transaction.
-- Here's the exact command to copy/paste:
-- SHUTDOWN WITH NOWAIT

----------------------------------------------------------------------------------------------------------
--- First recovery scenario - TAIL of the log IS accessible.
----------------------------------------------------------------------------------------------------------
-- With SQL Server shutdown - change to the data directory, then delete the data file:
-- cd \pro*\mi*sql*\mssql.1\mssql\data
-- del AdventureWorksCopy_Data.mdf
-- Once the data file is gone, restart sql server
-- net start mssql$SQLDev01 (or use the UI)

-- check databases... AdventureWorksCopy is damaged and pending recovery (never recovered)
sp_helpdb
go
-- not listed!

SELECT * FROM sys.databases
-- shows pending_recovery
-- Review Error Log for recovery errors:

-- can we get in?
USE AdventureWorksCopy
go

-- ERROR 945
--Msg 945, Level 14, State 2, Line 1
--Database 'AdventureWorksCopy' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

-- First reaction... can you backup the TAIL of the log?
BACKUP LOG AdventureWorksCopy
	TO DISK = N'C:\SQLTemp\AventureWorksCopy.bak'
	WITH NO_TRUNCATE -- this is the option that allows you to backup an "orphaned transaction log"
go

-- Now you can recover!

RESTORE DATABASE AdventureWorksCopy
	FROM DISK = N'C:\SQLTemp\AventureWorksCopy.bak'
	WITH FILE = 1, NORECOVERY

RESTORE LOG AdventureWorksCopy
	FROM DISK = N'C:\SQLTemp\AventureWorksCopy.bak'
	WITH FILE = 2, RECOVERY
go

USE AdventureWorksCopy
go

SELECT *
FROM Person.Contact AS C
WHERE ContactID < 10
go

-- Notice that T1 is there but Contact 3 has not changed. The reason is that T3 had not yet committed.
-- By recovering the log you can get UP-TO-THE-MINUTE recovery.

-- What if the log is damaged?
-- This is an optional scenario for you to test!

----------------------------------------------------------------------------------------------------------
--- RE-setup Recovery Scenario
----------------------------------------------------------------------------------------------------------

USE master
go
RESTORE DATABASE [AdventureWorksCopy] 
FROM  DISK = N'C:\SQLTemp\AdventureWorksOriginal.bak' 
WITH  FILE = 1,  
	MOVE N'AdventureWorks_Data' 
		TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksCopy_Data.mdf',  
	MOVE N'AdventureWorks_Log' 
		TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksCopy_Log.ldf',  
NOUNLOAD,  STATS = 10
go

-- They're checking for cases where someone overwrites the 
-- backup before accessing the tail...
-- need to add REPLACE 

RESTORE DATABASE [AdventureWorksCopy] 
FROM  DISK = N'C:\SQLTemp\AdventureWorksOriginal.bak' 
WITH  FILE = 1,  
	MOVE N'AdventureWorks_Data' 
		TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksCopy_Data.mdf',  
	MOVE N'AdventureWorks_Log' 
		TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksCopy_Log.ldf',  
NOUNLOAD,  STATS = 10, REPLACE
go

ALTER DATABASE AdventureWorksCopy
	SET RECOVERY FULL
go

-- Recreate the starting point for recovery
BACKUP DATABASE AdventureWorksCopy
	TO DISK = N'C:\SQLTemp\AventureWorksCopy.bak'
	WITH INIT
go

-- Now we can begin to perform transactions as well as manage those transactions
-- through transaction log backups.

USE AdventureWorksCopy
go

SELECT *
FROM Person.Contact AS C
WHERE ContactID < 10
go

-- See that T1 is no longer visible. We recovered to the original version.

-- This is transaction 1 from slide titled: "Transaction Recovery & Checkpoints"
UPDATE Person.Contact
	SET FirstName = 'T1' -- and this will commit b4 checkpoint
	WHERE ContactID = 1
go

-- This is transaction 3 from slide titled: "Transaction Recovery & Checkpoints"
BEGIN TRANSACTION
UPDATE Person.Contact
	SET FirstName = 'T3' -- and this will NOT commit b4 checkpoint
	WHERE ContactID = 3

-- go to another window (or here) and run a CHECKPOINT in this database

-- shutdown the server with nowait (power failure, etc.)
-- this MUST be completed in another window as SHUTDOWN cannot
-- be part of a transaction.
-- Here's the exact command to copy/paste:
-- SHUTDOWN WITH NOWAIT

----------------------------------------------------------------------------------------------------------
--- Second recovery scenario - TAIL of the log IS NOT accessible.
----------------------------------------------------------------------------------------------------------
-- With SQL Server shutdown - change to the data directory, then delete the data file:
-- cd \pro*\mi*sql*\mssql.1\mssql\data
-- del AdventureWorksCopy_Log.ldf
-- Once the LOG file is gone, restart sql server
-- net start mssql$SQLDev01 (or use the UI)

-- check databases... AdventureWorksCopy is once again not visible and shows pending_recovery
sp_helpdb
go
-- not listed!

SELECT * FROM sys.databases
-- shows pending_recovery
-- Review Error Log for recovery errors:

-- can we get in?
USE AdventureWorksCopy
go

-- First reaction... can you backup the TAIL of the log?
BACKUP LOG AdventureWorksCopy
	TO DISK = N'C:\SQLTemp\AventureWorksCopy.bak'
	WITH NO_TRUNCATE -- this is the option that allows you to backup an "orphaned transaction log"
go

-- NO!!
-- File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksCopy_Log.ldf" may be incorrect.
-- Server: Msg 3447, Level 16, State 1, Line 1
-- Could not activate or scan all of the log files for database 'AdventureWorksCopy'.
-- Server: Msg 3013, Level 16, State 1, Line 1
-- BACKUP LOG is terminating abnormally.
--
-- What can you do?
-- You really have two options:
--	Restore from last known good (meaning the absolutely LAST image you have)
--	What if you don't have that?

USE master
go

-- Use the sp_SetEmergencyMode to change a database's status.
ALTER DATABASE AdventureWorksCopy
SET EMERGENCY
go

-- Now can we get in?
USE AdventureWorksCopy
go

-- Sure, but what's the state of the database?

SELECT *
FROM Person.Contact AS C
WHERE ContactID < 10
go


-- So - are you sure you can guarantee that all your data is consistent?
-- SQL Server can't - at least not without the log. This is why Microsoft
-- says that when a database is suspect you should begin your recovery process...

-- So - how much data are you willing to lose?

-- Topics to read from BOL:
-- Database States 