SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Alter Multiple Databases at Once

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.

Matthew McGiffen DBA

I started my career in IT as a developer with Visual Basic, C# and ASP working with SQL Server backends. For the last ten years though I’ve focused on the SQL Server side of things. Currently I’m working for a leading software vendor for the financial industry as a DBA, doing a mixture of development and data architecture. If I have a speciality it is in making things run faster.

Comments

Leave a comment on the original post [matthewmcgiffen.com, opens in a new window]

Loading comments...