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 = 8 

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