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.
Leave a Reply