SQLStarters

Changing options for multiple databases can be time-consuming when done one database at the time. With just few databases you might be fine using the Management Studio and doing changes through the GUI but when you have dozens, or hundreds, of databases and only some of them are having wrong options (like that stupid AUTO_CLOSE one) you need to replace, you’re going to want to use scripts for it.

Here are few examples on how to make your job bit more easier. We’ll start by doing something simple, such as using ALTER DATABASE statement to SET some options for our databases. There are few ways to do this, one is to use cursors. Yes, I do use cursors for administrative purposes, they’re just fine for that 🙂 Let’s say we want to set all our user databases to FULL RECOVERY model.

Example 1.

DECLARE @dbname VARCHAR(40)
DECLARE @alterquery NVARCHAR(1000)
DECLARE DB_Names CURSOR
FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4 AND
    recovery_model_desc = N'SIMPLE' AND
    state_desc = N'ONLINE'
OPEN DB_Names
FETCH NEXT FROM DB_Names INTO @dbname
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @alterquery=('ALTER DATABASE ' +@dbname+ ' SET RECOVERY FULL;')
    PRINT @alterquery
    FETCH NEXT FROM DB_Names INTO @dbname
END
CLOSE DB_Names
DEALLOCATE DB_Names

What the script above does is that it generates an ALTER DATABASE statement for each user database that has SIMPLE recovery model set. It doesn’t execute anything though, so you need to copy-paste the ALTER DATABASE statements to new window and execute it manually. There’s however a better way to generate just the statements, where you would use the above approach is to actually run the statements on the same go by changing the PRINT @alterquery to EXECUTE master..sp_executesql @alterquery. It’s usually a good idea to see what your scripts are doing by PRINTing the output first before actually running them though.

Now the second example doesn’t involve cursors and is bit more safe because it doesn’t execute anything, it just prints out the ALTER statement for you to execute. We’ll be using handy little function called QUOTENAME that’ll create delimited Unicode string out of the name column values we get from sys.databases.

Example 2.

SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET RECOVERY FULL;'
FROM sys.databases
	WHERE database_id > 4 AND
	state_desc = N'ONLINE' AND
	recovery_model_desc = N'SIMPLE'

It’s shorter, simpler and as a bonus you don’t need to use a cursor if you really, really don’t want to. But what if you need to do something more complex? One example of a bit more complex change is to set options to database files, maybe setting an autogrowth value to all your transaction log files (highly recommended). Well, you can use the same query as above with some minor alterations.

Example 3.

SELECT 'ALTER DATABASE ' + QUOTENAME(DB_NAME(database_id)) + ' MODIFY FILE (NAME=' + QUOTENAME(name) + ', FILEGROWTH=256MB);'
FROM sys.master_files
	WHERE database_id > 4 AND
	state_desc = N'ONLINE' AND
	type = 1;

Easy, yes? It’s also worth noting that while writing queries like those above will take some time, not a lot mind you, it’ll pay off if you can make repeatable tasks so much more faster in the future.