Alter Multiple Databases at Once

~ Matthew McGiffen

This is a quick and dirty method I often use when I want to make a change to multiple databases on a SQL Server instance, usually based on a criteria.

It’s a fairly basic level thing to do, and while it is probably trivial to a SQL expert, I find most beginners wouldn’t consider it. So, I thought it was worth sharing.

Let’s say that I have the following set of databases:

MultipleDBs1

I’ve got a set of databases suffixed _dev, and another set suffixed _uat, as well as a couple of others.

Let’s say I need to take the _uat databases offline. I could do that one at a time through the GUI in SSMS. Or I could write SQL statements to do that.

Or, I could write a query to generate the SQL for me. In this case I only have three databases so it’s not going to save me a lot of time, but when you have tens or hundreds…

First, I write a query to make sure I can select out just the databases I want:

SELECT name
FROM sys.databases
WHERE name LIKE '%_uat';

And I view the results:

MultipleDBs2

That’s what I wanted. So, the next stage is to alter that query to generate the SQL statements to take those databases offline. The SQL to set a single database offline is:

ALTER DATABASE [{Database Name}] SET OFFLINE;

So, I alter my query as follows:

SELECT 'ALTER DATABASE [' + name + '] SET OFFLINE;'
FROM sys.databases
WHERE name LIKE '%_uat';

Which gives me:

MultipleDBs3

I can then copy that results into my query window and execute it as a block:

ALTER DATABASE [Users_uat] SET OFFLINE;
ALTER DATABASE [App_uat] SET OFFLINE;
ALTER DATABASE [Config_uat] SET OFFLINE;

Usually I’ll execute the first line initially to make sure I haven’t messed up the syntax and then I’ll run the rest in one go.

If we look at the list of databases again in the Object Explorer you can see it’s done the job:

MultipleDBs4

You can get clever and create a cursor to execute the result-set one at a time using dynamic SQL, but if you’re just performing a one-off task and you want the quickest solution then I find this the best method.

You can use this technique for all sorts of ad-hoc admin tasks, so it’s a very useful little tool to have in your belt.

Also, you’re not just restricted to performing administrative actions on databases, it could be any set of SQL objects. Personally I find doing things using methods like this a lot easier than messing about with PowerShell.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

4 thoughts on “Alter Multiple Databases at Once”

  1. I agree. I’ve used this method for years to generate multiple statements. One of my personal favorites is a script that generates code to drop and then restore all the FK’s on a table. Glad to know someone else is doing this.

    Reply

  2. Hello, Matthew,
    I never put UAT/DEV/Prod on one server/instance. Here is what I normally do.

    DECLARE @sqlcmd VARCHAR(MAX)=”;

    SET DEADLOCK_PRIORITY HIGH;
    SELECT @sqlcmd+=’
    ALTER DATABASE ‘+QUOTENAME(name)+’ SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE ‘+QUOTENAME(name)+’ SET OFFLINE;
    ALTER DATABASE ‘+QUOTENAME(name)+’ SET MULTI_USER WITH NO_WAIT;’
    FROM sys.databases
    WHERE name LIKE ‘%_uat’;
    EXEC (@sqlcmd);

    SET DEADLOCK_PRIORITY NORMAL;
    GO

    Peng

    Reply

    1. Hi Frank, with the Go statements, you’re a bit stuck (use dynamic SQL). If you can lose them, then you can edit it to be on one line using statement terminators:

      USE [MYDATABASE]; DBCC SHRINKFILE (N’MYDATABASE_log’ , 100);

      You can’t have a statement terminator after a Go though unfortunately (as it’s not strictly SQL)

      Reply

Leave a Reply