Per database tasks

Often as a DBA I am required to perform a particular task on every database, or some subset of databases. If it is the same task for each database then it makes sense to automate this in some way.

Two ways to do this are using the undocumented sp_MSforeachdb stored procedure and the other is with a sort of DBA assisted dynamic SQL (I am co-opting this term with no other authorization than this is how I think of it).

The first method is useful when a simple action is being done to all databases, while the second method is a little easier to filter and gives the DBA a bit more granular control.

Let’s for the sake of something to do consider changing recovery models into full.

Using sp_MSforeachdb the command is simple:

exec master..sp_MSforeachdb 'ALTER DATABASE ? SET RECOVERY FULL';

Two things to notice: The question mark acts as the place holder for the database name. This command will change all appropriate databases into full recovery but will also throw an error because it will also attempt to do this for tempdb and any database snapshots.

My favourite method is to use what I think of as DBA assisted dynamic SQL. Let’s perform the same action.

Start by querying the sys.databases catalog view for the databases we want to edit:

select name from sys.databases where recovery_model_desc != 'FULL';

Now we build a query that concatenates the names from this list into a series of alter database statements:

select 'ALTER DATABASE ' + name + ' SET RECOVERY FULL;'
 from sys.databases
 where recovery_model_desc != 'FULL';

This query was created simply by adding the strings on to either side of the name field from the original sys.databases query. The output is a series of alter database statements that can be copied and pasted into another query window.

What I like about this approach is that the DBA has a bit more granular control. I can scan the list and make any changes (like excluding tempdb) before I run the queries. I can choose to run as one batch or to run one at a time. I also have the option of copying this script out to use at a later date.

I find this option to be more versatile and easier to filter than the sp_MSforeachdb procedure (although this does have its place). The sp_MSforeachdb lends itself more easily to being scheduled as an automated job whereas the second method requires a little wrangling to pipe the output into an executeSQL statement.

Advertisements
This entry was posted in Database options, SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s