About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, June 12, 2015

vwDBScripts - Change database properties with ease

Some of these ALTER DATABASE commands are used when you are assessing a new server, or even for doing ongoing maintenance on an old server.

ALTER DATABASE [Admin] SET RECOVERY FULL 
ALTER DATABASE [Admin] SET RECOVERY SIMPLE     
ALTER DATABASE [Admin] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE     
ALTER DATABASE [Admin] SET SINGLE_USER WITH ROLLBACK IMMEDIATE   
ALTER DATABASE [Admin] SET MULTI_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE [Admin] SET READ_ONLY     
ALTER DATABASE [Admin] SET READ_WRITE    
ALTER DATABASE [Admin] SET AUTO_SHRINK OFF WITH NO_WAIT    
ALTER DATABASE [Admin] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [Admin] SET OFFLINE WITH ROLLBACK IMMEDIATE 
ALTER DATABASE [Admin] SET ONLINE  
ALTER DATABASE [Admin] SET COMPATIBILITY_LEVEL =

It is much more efficient to do this via T-SQL rather than by right-clicking the database and modifying the properties in the GUI.  Using a script will give you repeatability.  

With this view, you can quickly identify the database properties that need to be changed, such as turning on CHECKSUM or putting the recovery model to SIMPLE or FULL as required.  Then simply look for the appropriate column containing the script and copy/paste into a new window.  All the other columns available from sys.databases are included for reference, especially if you want to add other columns depending on your need.

SELECT Instance, Name, DBID, CreateDate, SqlChecksum
FROM vwDBScripts
WHERE PageVerify <> 'CHECKSUM'

I put this view into my Admin database which I have on all servers which I maintain.  Makes administration much more manageable. 


USE Admin

IF Object_ID('dbo.vwDBScripts') Is Not Null
      DROP VIEW dbo.vwDBScripts
go

CREATE VIEW dbo.vwDBScripts
AS
/*    DATE        AUTHOR            REMARKS
      6/4/15      PPaiva            Initial creation.
      6/14/15     PPaiva            Added column MirroringState.
     

      SELECT *
      FROM vwDBScripts
      ORDER By Name

      SELECT Instance, Name, PageVerify, SqlChecksum
      FROM vwDBScripts
      WHERE PageVerify <> 'CHECKSUM'

      SELECT Instance, Name, RecoveryModel, SqlRecoveryFull, SqlRecoverySimple
      FROM vwDBScripts
--    WHERE RecoveryModel = 'SIMPLE'
      WHERE RecoveryModel = 'FULL'

      SELECT Instance, Name, AutoShrink, SqlAutoShrinkOff
      FROM vwDBScripts
      WHERE AutoShrink = 1

      SELECT db_name(database_id) DB, *
      FROM sys.database_mirroring m     

*/
SELECT  ServerProperty('ServerName') Instance,
            name,
            d.database_id DBID,
            CASE WHEN name In ('master', 'model', 'msdb', 'tempdb', 'distribution')
                        THEN 1
                        ELSE 0
                              END IsSys,
            create_date CreateDate,
            compatibility_level CompLevel,
            user_access_desc UserAccess,
            is_read_only ReadOnly,
            is_auto_close_on AutoClose,
            is_auto_shrink_on AutoShrink,
            state_desc StateDesc,
            recovery_model_desc RecoveryModel,

            CASE WHEN m.mirroring_state = 0 THEN 'Suspended'
                   WHEN m.mirroring_state = 1 THEN 'Disconnected from partner'
                   WHEN m.mirroring_state = 2 THEN 'Synchronizing'
                   WHEN m.mirroring_state = 3 THEN 'Pending Failover'
                   WHEN m.mirroring_state = 4 THEN 'Synchronized'
                   WHEN m.mirroring_state = 5 THEN 'Partners not synchronized. Failover not possible.'
                   WHEN m.mirroring_state = 6 THEN 'Partners synchronized.  Failover possible.'
                   WHEN m.mirroring_state Is Null THEN 'NONE'
                   ELSE 'undefined'
                   END MirroringState,

            page_verify_option_desc PageVerify,
            collation_name,
            is_in_standby,
            'ALTER DATABASE [' + name + '] SET RECOVERY FULL' SqlRecoveryFull,
            'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE' SqlRecoverySimple,
            'ALTER DATABASE [' + name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE' SqlRestrictedUser,
            'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' SqlSingleUser,
            'ALTER DATABASE [' + name + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE' SqlMultiUser,
            'ALTER DATABASE [' + name + '] SET READ_ONLY' SqlReadOnly,
            'ALTER DATABASE [' + name + '] SET READ_WRITE' SqlReadWrite,
            'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF WITH NO_WAIT' SqlAutoShrinkOff,
            'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM' SqlChecksum,
            'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE' SqlOffline,
            'ALTER DATABASE [' + name + '] SET ONLINE' SqlOnline,
            'ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 8  -- 2008 = 100, 2012 = 110, 2014 = 120' SqlCompatibility,

            is_cleanly_shutdown,
            is_supplemental_logging_enabled,
            snapshot_isolation_state,
            snapshot_isolation_state_desc,
            is_read_committed_snapshot_on,
            recovery_model,
            page_verify_option,
            is_auto_create_stats_on,
            is_auto_update_stats_on,
            is_auto_update_stats_async_on,
            is_ansi_null_default_on,
            is_ansi_nulls_on,
            is_ansi_padding_on,
            is_ansi_warnings_on,
            is_arithabort_on,
            is_concat_null_yields_null_on,
            is_numeric_roundabort_on,
            is_quoted_identifier_on,
            is_recursive_triggers_on,
            is_cursor_close_on_commit_on,
            is_local_cursor_default,
            is_fulltext_enabled,
            is_trustworthy_on,
            is_db_chaining_on,
            is_parameterization_forced,
            is_master_key_encrypted_by_server,
            is_published,
            is_subscribed,
            is_merge_published,
            is_distributor,
            is_sync_with_backup,
            source_database_id,
            owner_sid,
            user_access,
            state,
            service_broker_guid,
            is_broker_enabled,
            log_reuse_wait,
            log_reuse_wait_desc,
            is_date_correlation_on
FROM sys.databases d
JOIN sys.database_mirroring m
      ON d.database_id = m.database_id