How to set Page_verify

Good day greeting to every one.

   In my company , auditing is going on for SQL server databases. They want to know that whether checksum is enable on page_verify.

Why this option will be used for databases?

Page_verify can be set for 3 options

1. CHECKSUM
2. TORN_PAGE_DETECTION
3. NONE

Dictionary meaning : a digit representing the sum of the correct digits in a piece of stored or transmitted digital data, against which later comparisons can be made to detect errors in the data.

CHECKSUM : When this option is enabled , SQL server database engine will calculate a checksum over the contents of whole page and store the values in page header. When a page written to disk or page is reading from disk than the checksum is recompute and compared to the checksum value that is stored in the page header.
By this it helps to provide a high level of data file integrity.


How to enable this Option:

Right Click on database --> choose option properties --> Click on options --> search for Page_Verify

• Script to list All user databases and their page verify option set.

SELECT  name AS [Database] ,
CASE WHEN page_verify_option = 0 THEN 'NONE'
WHEN page_verify_option = 1 THEN 'TORN_PAGE_DETECTION'
WHEN page_verify_option = 2 THEN 'CHECKSUM'
END AS [Page Verify]
FROM    sys.databases
WHERE   database_id > 4

• Change PAGE_VERIFY Setting to CHECKSUM for Database Using TSQL Query

Use Master
GO

SELECT
    'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;'
           AS [Change Page Verify Settings to Checksum]
FROM SYS.DATABASES
        WHERE STATE_DESC ='ONLINE' AND page_verify_option_desc != 'CHECKSUM'
GO


To be continued.....


References : http://www.sqlskills.com/