/*============================================================================
  File:     6_DBCC_CHECKDB.sql

  Summary:  This script reviews how to run CHECKDB, and
			also steps through an example of corruption
			in a nonclustered index.
			Examples of corrupt databases can be found here:
			http://www.sqlskills.com/sql-server-resources/sql-server-demos/

  Date:     April 2015

  SQL Server Version: 2005/2008/2008R2/2012/2014
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  (c) 2015, SQLskills.com. All rights reserved.

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

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  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.
============================================================================*/

/*
	Run CHECKDB with no options
*/
DBCC CHECKDB (N'AdventureWorks2014') 



/*
	Run CHECKDB with recommended options
*/
DBCC CHECKDB (N'AdventureWorks2014') WITH ALL_ERRORMSGS, NO_INFOMSGS;



/*
	Run CHECKDB with PHYSICAL_ONLY option (larger DBs)
*/
DBCC CHECKDB (N'AdventureWorks2014') WITH PHYSICAL_ONLY; 



/*
	Estimate how much tempdb space CHECKDB will need
*/
DBCC CHECKDB (N'AdventureWorks2014') WITH ESTIMATEONLY;



/*
	Corruption Example 1
*/

USE AdventureWorks2014_Corrupt;
GO

SELECT BusinessEntityID, LoginID
FROM AdventureWorks2014_Corrupt.HumanResources.Employee 
WHERE LoginID LIKE 'adventure-works\jill%';


/*
	note that in earlier versions ( < 2008R2SP2 )
	you would get an error about data movement due to NOLOCK, like this:
*/
SELECT BusinessEntityID, LoginID
FROM AdventureWorks2014_Corrupt.HumanResources.Employee WITH(NOLOCK)
WHERE LoginID LIKE 'adventure-works\jill%';
/*
	it has nothing to do with corruption
	don't just add WITH (NOLOCK) to your query
*/


/*
	do not take the database offline!  
	do not detach the database!
	do not stop the instance!
*/


/*
	run a full check
*/
DBCC CHECKDB (N'AdventureWorks2014_Corrupt') WITH ALL_ERRORMSGS, NO_INFOMSGS;

DBCC CHECKDB (N'AdventureWorks2014_Corrupt') WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS;
GO


/*
	for a larger system, might just check the table first:
*/
USE [AdventureWorks2014_Corrupt];
GO
DBCC CHECKTABLE (N'HumanResources.Employee') WITH ALL_ERRORMSGS, NO_INFOMSGS;


/*
	then run allocation and catalog checks
*/
DBCC CHECKALLOC (N'AdventureWorks2014_Corrupt') WITH ALL_ERRORMSGS, NO_INFOMSGS;

DBCC CHECKCATALOG (N'AdventureWorks2014_Corrupt');



/*
	rebuilding index will not work
*/
USE [AdventureWorks2014_Corrupt];
GO
ALTER INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee] REBUILD;
GO



/*
	Option 1: Drop and then recreate index
	If constraints exist, do this within a transaction or in single user mode
*/
USE [AdventureWorks2014_Corrupt];
GO

BEGIN TRANSACTION;

	DROP INDEX  [HumanResources].[Employee].[AK_Employee_LoginID]

	CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee] ([LoginID])

COMMIT TRANSACTION;

/*
	Option 2: Disable, then rebuild
	If constraints exist, do this within a transaction or in single user mode
*/
USE [AdventureWorks2014_Corrupt];
GO

BEGIN TRANSACTION;

	ALTER INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee] DISABLE;

	ALTER INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee] REBUILD;

COMMIT TRANSACTION;

/*
	verify corruption is fixed
*/
USE [AdventureWorks2014_Corrupt];
GO

DBCC CHECKTABLE (N'HumanResources.Employee') WITH ALL_ERRORMSGS, NO_INFOMSGS;

SELECT BusinessEntityID, LoginID
FROM AdventureWorks2014_Corrupt.HumanResources.Employee 
WHERE LoginID LIKE 'adventure-works\jill%';

DBCC CHECKDB (N'AdventureWorks2014_Corrupt') WITH ALL_ERRORMSGS, NO_INFOMSGS;


/*
	Corruption 2
*/
USE [AdventureWorks2014_Corrupt2];
GO

SELECT BusinessEntityID, LoginID, JobTitle 
FROM AdventureWorks2014_Corrupt2.HumanResources.Employee 
WHERE LoginID LIKE 'adventure-works\charles%'


/*
	do not stop the instance!
	do not take the database offline!  
	do not detach the database!
*/

/*
	Run CHECKDB
*/
DBCC CHECKDB (N'AdventureWorks2014_Corrupt2') WITH ALL_ERRORMSGS, NO_INFOMSGS;

/*
	for a larger system, might just check the table first:
*/
USE [AdventureWorks2014_Corrupt2];
GO
DBCC CHECKTABLE (N'HumanResources.Employee') WITH ALL_ERRORMSGS, NO_INFOMSGS;


/*
	then run allocation and catalog checks
*/
DBCC CHECKALLOC (N'AdventureWorks2014_Corrupt2') WITH ALL_ERRORMSGS, NO_INFOMSGS;

DBCC CHECKCATALOG (N'AdventureWorks2014_Corrupt2');


/*
	how do you fix this?
	we have corruption in the clustered index...so you can't just rebuild it
	restore from a known, good backup?
	repair, and also restore a known, good backup and try to move data that was removed?
*/

DBCC CHECKDB 
[
    [ ( database_name | database_id | 0
        [ , NOINDEX 
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    ) ]
    [ WITH 
        {
            [ ALL_ERRORMSGS ]
            [ , EXTENDED_LOGICAL_CHECKS ] 
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]
        }
    ]
]

/*
	REPAIR_REBUILD -> only does repairs that have no data loss

	REPAIR_FAST -> THIS DOES NOTHING
	
	REBUILD_ALLOW_DATA_LOSS -> YOU WILL LOSE DATA
*/


/*
	BEFORE YOU RUN ANY REPAIR:
		Take a backup
		Restore the backup
		Run repair against the restore database

	Repair requires single-user mode
	(whether you run CHECKDB or CHECKTABLE)
*/
DBCC CHECKDB ([AdventureWorks2014_Corrupt2], REPAIR_REBUILD);
GO

USE [AdventureWorks2014_Corrupt2];
GO
DBCC CHECKTABLE ('HumanResources.Employee', REPAIR_REBUILD);
GO

USE [master]
GO
ALTER DATABASE [AdventureWorks2014_Corrupt2] SET  SINGLE_USER WITH NO_WAIT
GO

USE [AdventureWorks2014_Corrupt2];
GO
DBCC CHECKTABLE ('HumanResources.Employee', REPAIR_REBUILD);
GO

/*
	read the output from this!!
*/
DBCC CHECKTABLE ('HumanResources.Employee', REPAIR_ALLOW_DATA_LOSS);
GO

/*
	did we fix everything?
*/
DBCC CHECKDB (N'AdventureWorks2014_Corrupt2') WITH ALL_ERRORMSGS, NO_INFOMSGS;

/*
	Score!
	Oh wait...we lost some data didn't we?
	How do we know what we lost?
	Restore the most recent, good copy and see what we can find that's missing
	Check counts first...
*/
SELECT COUNT(*)
FROM [AdventureWorks2014_Corrupt2].[HumanResources].[Employee];
GO

SELECT COUNT(*)
FROM [AdventureWorks2014].[HumanResources].[Employee];
GO

/*
	We're missing 45 rows
	Now do a compare
*/
SELECT *
FROM [AdventureWorks2014].[HumanResources].[Employee] [source]
LEFT OUTER JOIN [AdventureWorks2014_Corrupt2].[HumanResources].[Employee] [repaired] 
	ON [source].[BusinessEntityID] = [repaired].[BusinessEntityID]
WHERE [repaired].[BusinessEntityID] IS NULL;



