-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- 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 pubs
	TO DISK = N'C:\pubsOriginal.bak'
	WITH INIT

-- For this demo, you need to change the recovery model to full
ALTER DATABASE pubs
	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 pubs
	TO DISK = N'C:\pubsbackup.bak'
	WITH INIT
go

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

USE pubs
go

-- Again, for simplicity - we'll make changes to the titles table in order to 
-- setup the same set of transactions as in the slide: "Transaction Recovery & Checkpoints"

SELECT t.title_id 	AS [Title ID]
		, t.title		AS [Title]
FROM dbo.titles AS t
ORDER BY [Title ID]
go

-- This is transaction 1 from slide titled: "Transaction Recovery & Checkpoints"
UPDATE titles
	SET title = 'T1 commit b4 checkpoint - committed'
	WHERE title_id = 'BU1032'
go

-- This is transaction 3 from slide titled: "Transaction Recovery & Checkpoints"
BEGIN TRANSACTION
UPDATE titles
	SET title = 'T2 change b4 checkpoint - NOT committed'
	WHERE title_id = 'BU1111'

-- 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\data
-- del pubs.mdf
-- Once the data file is gone, restart sql server
-- net start mssqlserver

-- check databases... pubs should be suspect
sp_helpdb
go
-- not listed!

-- can we get in?
USE pubs
go

-- ERROR 945
-- Server: Msg 945, Level 14, State 2, Line 1
-- Database 'pubs' 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 pubs
	TO DISK = N'C:\pubsbackup.bak'
	WITH NO_TRUNCATE -- this is the option that allows you to backup an "orphaned transaction log"
go

-- Now you can recover!

RESTORE DATABASE pubs
	FROM DISK = N'C:\pubsbackup.bak'
	WITH FILE = 1, NORECOVERY

RESTORE LOG pubs
	FROM DISK = N'C:\pubsbackup.bak'
	WITH FILE = 2, RECOVERY
go

USE pubs
go

SELECT t.title_id 	AS [Title ID]
		, t.title		AS [Title]
FROM dbo.titles AS t
ORDER BY [Title ID]
go

-- Notice that T1 is there but T2 is NOT. The reason is that T2 had not yet committed.
-- By recovering the log you can get UP-TO-THE-MINUTE recovery.

-- What if the log is damaged?

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

BACKUP DATABASE pubs
	TO DISK = N'C:\pubsbackup.bak'
	WITH INIT -- you still have the original pubs backup so we're going to overwrite these.
go

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

USE pubs
go

-- Again, for simplicity - we'll make changes to the titles table in order to 
-- setup the same set of transactions as in the slide: "Transaction Recovery & Checkpoints"

SELECT t.title_id 	AS [Title ID]
		, t.title		AS [Title]
FROM dbo.titles AS t
ORDER BY [Title ID]
go

-- This is transaction 1 from slide titled: "Transaction Recovery & Checkpoints"
UPDATE titles
	SET title = 'T1 commit b4 checkpoint - committed in SECOND test'
	WHERE title_id = 'BU1111'
go

-- This is transaction 3 from slide titled: "Transaction Recovery & Checkpoints"
BEGIN TRANSACTION
UPDATE titles
	SET title = 'T2 change b4 checkpoint - NOT committed in SECOND test'
	WHERE title_id = 'BU2075'

-- 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 LOG file:
-- cd \pro*\mi*sql*\mssql\data
-- del pubs_log.ldf
-- Once the data file is gone, restart sql server
-- net start mssqlserver

-- check databases... pubs is once again SUSPECT
sp_helpdb
go
-- not listed!

-- can we get in?
USE pubs
go

-- ERROR 945
-- Server: Msg 945, Level 14, State 2, Line 1
-- Database 'pubs' 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 pubs
	TO DISK = N'C:\pubsbackup.bak'
	WITH NO_TRUNCATE -- this is the option that allows you to backup an "orphaned transaction log"
go

-- NOPE!!
-- Server: Msg 3447, Level 16, State 1, Line 1
-- Could not activate or scan all of the log files for database 'pubs'.
-- Server: Msg 3013, Level 16, State 1, Line 1
-- BACKUP LOG is terminating abnormally.
-- Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf' may be incorrect.

-- 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.
EXEC sp_SetEmergencyMode 'pubs'

-- Now can we get in?
USE pubs
go

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

SELECT t.title_id 	AS [Title ID]
		, t.title		AS [Title]
FROM dbo.titles AS t
ORDER BY [Title ID]
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?


















-- What's your recovery?
-- First see if you can access the log?
-- Backup the tail of the log (when possible)
-- Recover from backups...

-- So - if SQL Server had the log then we wouldn't see this partial transaction...
-- let's see what would have happened if we hadn't had media failure, just power
-- failure?

-- shutdown the server and delete the data but put back the log...
sp_configure 'allow updates', 1
go
reconfigure with override
go

update master.dbo.sysdatabases
	set status = 16
	where name = 'pubs'
go

sp_configure 'allow updates', 0
go
reconfigure with override
go

shutdown with nowait
go

use pubs
go

-- same problem - error 945

-- pubs is suspect but we have the log from which we can recover!
-- How?
-- backup the tail of the transaction log

-- First, can we get any data?
SELECT t.title_id 	AS [Title ID]
	, t.title		AS [Title]
FROM dbo.titles AS t
ORDER BY [Title ID]
go

-- Can we do anything?
-- Backup the tail of the transaction log!

BACKUP LOG pubs
	TO DISK = N'C:\pubsbackup.bak'
	WITH NOINIT, NO_TRUNCATE

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

SELECT t.title_id 	AS [Title ID]
	, t.title		AS [Title]
FROM dbo.titles AS t
ORDER BY [Title ID]
go

-- Restore the original version of the database back to the start:
DROP DATABASE pubs
go
RESTORE DATABASE pubs
	FROM DISK = N'C:\pubsbackup.bak'
	WITH RECOVERY, REPLACE 