How to change PAGE_VERIFY on multiple databases and multiple instances

In the recent days I wanted to change the PAGE_VERIFY option on all databases and make sure I am using CHECKSUM everywhere. First question I guess would be “Why?”. The answer lies here.

The article gives a very good example on how to change the PAGE_VERIFY for all databases on one instance, but how do we do that when we use a CMS and multi-server queries? I managed to write this simple statement, that is doing the work. It needs a lot of polishing though (for example narrowing down the list of databases, where we do not need the page verification mechanism). But anyway, it is really good point for starting the task.

--mass change the PAGE_VERIFY option for all instances and databases 
--that are not read only and have current page_verify = NONE or TORN_PAGE
Exec
sp_MSforeachdb '
use [?]
if (select count(*) from sys.databases where page_verify_option <2 and name=''?'' and database_id>4 and is_read_only = 0)=1
begin
alter database [?] set page_verify CHECKSUM WITH NO_WAIT
PRINT '': PAGE_VERIFY changed to CHECKSUM for database = '' + DB_NAME()
end'

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.