Provide table with list of databases to exclude

[philcart]

Hi

It would be good to have a table where we can put in a list of databases that we want to exclude from backup/maintenance/etc...

We have a number of user databases that are basically extracts of client data from the main production database. These databases are cleared and re-loaded every night before being transferred offsite. As such it doesn't make sense to include these databases in the maintenance plans.

In our dev/test/uat environments we also have a couple of "old" previous versions of vendor databases that we hold purely for data/schema comparison when we get new versions of the product.

Below is what I've hacked together as a "total" exclusion list, but it'd probably be more useful if we could exclude by functionality. eg: exclude from IndexOptimize, but include in backups

DECLARE @dbList nvarchar(max) = (
        -- get list of online, writable user databases
        SELECT STUFF((
            SELECT DISTINCT ',' + QUOTENAME(c.[name])
            FROM (
                SELECT [name] 
                FROM sys.databases
                WHERE [database_id] > 4
                    AND [state_desc] = 'ONLINE'
                    AND [is_read_only] = 0
                    AND [name] NOT IN (SELECT [dbName] FROM [dbo].[dbMaintExclusion])
            ) as c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''));
EXEC [dbo].[IndexOptimize]
    @Databases = @dbList,
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y',
    @Execute = 'N'
GO

Cheers Phil

[ArPharazon]

Hi Phil,

Just checking if you're aware that the @databases parameter already has equivalent functionality to exclude databases:

`The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).'

In my case, i'm using @databases = 'USER_DATABASES, -%DEV%, -%TEST%'

Cheers, Sean

[philcart]

@ArPharazon yes I am aware of that option.

We have a bunch of different databases with similar names, so our @databases parameter would be long, convoluted and easily misunderstood on what is actually included and excluded.

Also, having the list in a database table involves a simple data update to affect a change. Whereas having it in the command of the SQL Agent job would require change control approval in our case.

Cheers Phil

[rgblanks]

We also can have long strings of inclusion/exclusion - not only in database names but other parms like indexes. Often we will optimize a single index at a certain time compared to others. Having a table that would house "objects to include/exclude" would be easily populated and readable. (ObjectName, Type, OperationType, Include_Exclude) Values.. BigOLTPDB Database FullBackup Include MYOLAPDB Database FullBackup Exclude MyTable Table DBCC Exclude MyIndex Index IndexOptimize Include

We have various different schedules in large datawarehouse that have nightly etl and daily user changes. Scheduling and setting things up can get complicated.