June 22, 2010 at 12:01 am
We have more than 100 databases and we would like to alter the db option "page verify" from "none" to "checksum".
Do you know of any method to accomplish this task?
Thank you very much.
June 22, 2010 at 12:29 am
beppe_30 (6/22/2010)
We have more than 100 databases and we would like to alter the db option "page verify" from "none" to "checksum".Do you know of any method to accomplish this task?
Thank you very much.
I have written a script for that:
set nocount on
select 'alter database [' + name + '] set PAGE_VERIFY CHECKSUM;' + char(10) + 'go' + char(10)
from sys.databases
where database_id > 4
and page_verify_option <> 2
and [state] = 0
Send the "Results to Text" (not to grid)
Then copy the text output to New Query window and execute it.
June 22, 2010 at 6:08 am
Thanks for you replay 🙂
June 2, 2014 at 3:07 am
Hi There,
I want to find out the current setting of all the databases with current page_verify option in my environment.
If it is sql 2000 i want to set torn page detection on and if it is sql 2005 i want to set checksum enabled.
Thanks in advance.
June 2, 2014 at 5:43 am
i still have my SQL 2000 scripts saved, although i don't play with 2000 any more.
there's some server functions DATABASEPROPERTY and DATABASEPROPERTYEX that can pull the values you want , and if they both existed in SQL2000, you could use that.
Im not sure if DATABASEPROPERTYEX was introduced in 2000 or 2005.
if it's not available for 2000, then you have to parse the sysdatabases.status field.
SELECT
DATABASEPROPERTY(db_name(),'IsAnsiNullDefault') AS [IsAnsiNullDefault],
DATABASEPROPERTY(db_name(),'IsAnsiNullsEnabled') AS [IsAnsiNullsEnabled],
DATABASEPROPERTY(db_name(),'IsAnsiWarningsEnabled') AS [IsAnsiWarningsEnabled],
DATABASEPROPERTY(db_name(),'IsAutoClose') AS [IsAutoClose],
DATABASEPROPERTY(db_name(),'IsAutoCreateStatistics') AS [IsAutoCreateStatistics],
DATABASEPROPERTY(db_name(),'IsAutoShrink') AS [IsAutoShrink],
DATABASEPROPERTY(db_name(),'IsAutoUpdateStatistics') AS [IsAutoUpdateStatistics],
DATABASEPROPERTY(db_name(),'IsBulkCopy') AS [IsBulkCopy],
DATABASEPROPERTY(db_name(),'IsCloseCursorsOnCommitEnabled') AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTY(db_name(),'IsDboOnly') AS [IsDboOnly],
DATABASEPROPERTY(db_name(),'IsDetached') AS [IsDetached],
DATABASEPROPERTY(db_name(),'IsEmergencyMode') AS [IsEmergencyMode],
DATABASEPROPERTY(db_name(),'IsFulltextEnabled') AS [IsFulltextEnabled],
DATABASEPROPERTY(db_name(),'IsInLoad') AS [IsInLoad],
DATABASEPROPERTY(db_name(),'IsInRecovery') AS [IsInRecovery],
DATABASEPROPERTY(db_name(),'IsInStandBy') AS [IsInStandBy],
DATABASEPROPERTY(db_name(),'IsLocalCursorsDefault') AS [IsLocalCursorsDefault],
DATABASEPROPERTY(db_name(),'IsNotRecovered') AS [IsNotRecovered],
DATABASEPROPERTY(db_name(),'IsNullConcat') AS [IsNullConcat],
DATABASEPROPERTY(db_name(),'IsOffline') AS [IsOffline],
DATABASEPROPERTY(db_name(),'IsParameterizationForced') AS [IsParameterizationForced],
DATABASEPROPERTY(db_name(),'IsQuotedIdentifiersEnabled') AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTY(db_name(),'IsReadOnly') AS [IsReadOnly],
DATABASEPROPERTY(db_name(),'IsRecursiveTriggersEnabled') AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTY(db_name(),'IsShutDown') AS [IsShutDown],
DATABASEPROPERTY(db_name(),'IsSingleUser') AS [IsSingleUser],
DATABASEPROPERTY(db_name(),'IsSuspect') AS [IsSuspect],
DATABASEPROPERTY(db_name(),'IsTruncLog') AS [IsTruncLog],
DATABASEPROPERTY(db_name(),'Version') AS [Version]
SELECT
DATABASEPROPERTYEX(db_name(),'Collation') AS Collation,
DATABASEPROPERTYEX(db_name(),'ComparisonStyle') AS ComparisonStyle,
DATABASEPROPERTYEX(db_name(),'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTYEX(db_name(),'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTYEX(db_name(),'IsAnsiPaddingEnabled') AS IsAnsiPaddingEnabled,
DATABASEPROPERTYEX(db_name(),'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTYEX(db_name(),'IsArithmeticAbortEnabled') AS IsArithmeticAbortEnabled,
DATABASEPROPERTYEX(db_name(),'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTYEX(db_name(),'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTYEX(db_name(),'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTYEX(db_name(),'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTYEX(db_name(),'IsCloseCursorsOnCommitEnabled') AS IsCloseCursorsOnCommitEnabled,
DATABASEPROPERTYEX(db_name(),'IsFulltextEnabled') AS IsFulltextEnabled,
DATABASEPROPERTYEX(db_name(),'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTYEX(db_name(),'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTYEX(db_name(),'IsMergePublished') AS IsMergePublished,
DATABASEPROPERTYEX(db_name(),'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTYEX(db_name(),'IsNumericRoundAbortEnabled') AS IsNumericRoundAbortEnabled,
DATABASEPROPERTYEX(db_name(),'IsParameterizationForced') AS IsParameterizationForced,
DATABASEPROPERTYEX(db_name(),'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTYEX(db_name(),'IsPublished') AS IsPublished,
DATABASEPROPERTYEX(db_name(),'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTYEX(db_name(),'IsSubscribed') AS IsSubscribed,
DATABASEPROPERTYEX(db_name(),'IsSyncWithBackup') AS IsSyncWithBackup,
DATABASEPROPERTYEX(db_name(),'IsTornPageDetectionEnabled') AS IsTornPageDetectionEnabled,
DATABASEPROPERTYEX(db_name(),'LCID') AS LCID,
DATABASEPROPERTYEX(db_name(),'Recovery') AS Recovery,
DATABASEPROPERTYEX(db_name(),'SQLSortOrder') AS SQLSortOrder,
DATABASEPROPERTYEX(db_name(),'Status') AS Status,
DATABASEPROPERTYEX(db_name(),'Updateability') AS Updateability,
DATABASEPROPERTYEX(db_name(),'UserAccess ') AS UserAccess ,
DATABASEPROPERTYEX(db_name(),'Version') AS Version
you have to bitmath the status column in sysdatabases to extract the settings:
here's an old example or two:
select name as DBNAME,STATUS,
CASE WHEN (STATUS & 1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],
CASE WHEN (STATUS & 64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],
CASE WHEN (STATUS & 128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],
CASE WHEN (STATUS & 256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],
CASE WHEN (STATUS & 512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],
CASE WHEN (STATUS & 2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],
CASE WHEN (STATUS & 4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],
CASE WHEN (STATUS & 4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK_(ALTER_DATABASE)],
CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]
from master.dbo.sysdatabases
SELECT name as DBNAME,STATUS2,
CASE WHEN (STATUS2 & 16384) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_null_default_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 65536) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [concat_null_yields_null_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 131072) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [recursive_triggers_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 1048576) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [default_to_local_cursor_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 8388608) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [quoted_identifier_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 33554432) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [cursor_close_on_commit_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 67108864) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_nulls_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 268435456) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_warnings_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 536870912) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [full_text_enabled_(set_by_using_sp_fulltext_database)]
from master.dbo.sysdatabases
SELECT 'name: ' + [name] + CHAR(13) +
'autoclose: '+ MIN(CASE status & 1 WHEN 1 THEN 'True' ELSE 'False' END) + CHAR(13) +
'SELECT into/bulkcopy: '+ MIN(CASE status & 4 WHEN 4 THEN 'True' ELSE 'False' END) + CHAR(13) +
'trunc. log on chkpt: '+ MIN(CASE status & 8 WHEN 8 THEN 'True' ELSE 'False' END) + CHAR(13) +
'torn page detection: '+ MIN(CASE status & 16 WHEN 16 THEN 'True' ELSE 'False' END) + CHAR(13) +
'loading: '+ MIN(CASE status & 32 WHEN 32 THEN 'True' ELSE 'False' END) + CHAR(13) +
'pre recovery: '+ MIN(CASE status & 64 WHEN 64 THEN 'True' ELSE 'False' END) + CHAR(13) +
'recovering: '+ MIN(CASE status & 128 WHEN 128 THEN 'True' ELSE 'False' END) + CHAR(13) +
'Falset recovered: '+ MIN(CASE status & 256 WHEN 256 THEN 'True' ELSE 'False' END) + CHAR(13) +
'offline: '+ MIN(CASE status & 512 WHEN 512 THEN 'True' ELSE 'False' END) + CHAR(13) +
'read only: '+ MIN(CASE status & 1024 WHEN 1024 THEN 'True' ELSE 'False' END) + CHAR(13) +
'dbo use only: '+ MIN(CASE status & 2048 WHEN 2048 THEN 'True' ELSE 'False' END) + CHAR(13) +
'single user: '+ MIN(CASE status & 4096 WHEN 4096 THEN 'True' ELSE 'False' END) + CHAR(13) +
'emergency mode: '+ MIN(CASE status & 32768 WHEN 32768 THEN 'True' ELSE 'False' END) + CHAR(13) +
'autoshrink: '+ MIN(CASE status & 4194304 WHEN 4194304 THEN 'True' ELSE 'False' END) + CHAR(13) +
'cleanly shutdown: '+ MIN(CASE status & 1073741824 WHEN 1073741824 THEN 'True' ELSE 'False' END) + CHAR(13) +
'ANSI null default: '+ MIN(CASE status2 & 16384 WHEN 16384 THEN 'True' ELSE 'False' END) + CHAR(13) +
'concat null yields null: '+ MIN(CASE status2 & 65536 WHEN 65536 THEN 'True' ELSE 'False' END) + CHAR(13) +
'recursive triggers: '+ MIN(CASE status2 & 131072 WHEN 131072 THEN 'True' ELSE 'False' END) + CHAR(13) +
'default to local cursor: '+ MIN(CASE status2 & 1048576 WHEN 1048576 THEN 'True' ELSE 'False' END) + CHAR(13) +
'quoted identifier: '+ MIN(CASE status2 & 8388608 WHEN 8388608 THEN 'True' ELSE 'False' END) + CHAR(13) +
'cursor close on commit: '+ MIN(CASE status2 & 33554432 WHEN 33554432 THEN 'True' ELSE 'False' END) + CHAR(13) +
'ANSI nulls: '+ MIN(CASE status2 & 67108864 WHEN 67108864 THEN 'True' ELSE 'False' END) + CHAR(13) +
'ANSI warnings: '+ MIN(CASE status2 & 268435456 WHEN 268435456 THEN 'True' ELSE 'False' END) + CHAR(13) +
'full text enabled: '+ MIN(CASE status2 & 536870912 WHEN 536870912 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(13)
FROM master..sysdatabases
GROUP BY [name]
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply