April 28, 2009 at 1:57 am
Hi,
I have a number of databases of the same stucture and need to run my upgrade scripts on all of them at the same time. Do you know about a tool which can help to automate this? Ideally it should also be capable to do some automatic replacements. E.g. if the database name is MyDB_US, then replace all {country code} tags with "US".
Thanks.
April 28, 2009 at 2:17 am
If you're not already familiar with it, you may want to look at the undocumented procedure sp_msforeachdb, as well as sp_msforeachtable. Very handy and very flexible.
Redgate (the sponsor of this site) has some tools that will do what you're looking for, as well as Apex, if you need a more comprehensive set of tools.
David
April 28, 2009 at 7:09 am
As David has stated, sp_msforeachdb is a quick and dirty way ... but you will find it lacking pending on what you're attempting to do. Might want to look into cursors/loops.
Here is a quick example:
DECLARE
@DatabaseNameSYSNAME
,@SQLVARCHAR(128)
,@DebugBIT
SET @Debug = 0
DECLARE #db CURSOR STATIC LOCAL FOR
SELECT
[name]
FROM [master].[sys].[databases]
WHERE [database_id] > 4
AND [source_database_id] IS NULL
ORDER BY 1
OPEN #db
FETCH NEXT FROM #db INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =
'
USE ' + @DatabaseName + '
SELECT DB_NAME(), * FROM [sys].[database_files]
'
IF @Debug = 1
PRINT (@SQL)
ELSE
EXEC (@SQL)
FETCH NEXT FROM #db INTO @DatabaseName
END
CLOSE #db
DEALLOCATE #db
April 28, 2009 at 7:13 am
If you already have a TSQL script created, and easy fix would to put this line at the top:
USE [DatabaseName]
And then your code. If you need to run for multiple DBs with the same structure, just change the name and execute again.
April 28, 2009 at 6:51 pm
What I want to do is to run lets say 5 scripts against 10 databases (not all of the database, but selected ones) at the same time, so "USE DBName" or "sp_msforeachdb" are not an option - too much manual work. Besides the scripts can not be changed.
I fount that Red Gate has SQL Multi script, which does the job, but it does not allow to have automatic replacements within the script.
Suppose I have three databases called MyDB_US, MyDB_UK and MyDB_FR. The generic script for all of them would contain:
SET @JOBNAME = N'{country_code}_MyNewJob'
EXEC msdb.dbo.sp_add_job @job_name=@JOBNAME, ...
At the time of execution {country_code} tag would need to be replaced to "US" for MyDB_US, to "UK" for MyDB_UK, etc.
Besides the tool will fail if you try to create temp tables with the same name inside different scripts. I think there should be an option to run the scripts within separate sessions.
April 29, 2009 at 4:35 am
Cursor is your best option here, then. Use the below code to get your list of database names. Use the FETCH statement against this list. Plus, add a variable that uses substring to check the last two characters of your DB name. Use dynamic SQL for your update statement.
You might even be able to use ForEachDB against the list. Anyway, that should work. It's not simulatenous, but it'll give you the flexibility you need as well as the automation.
Select name
into dbo.#MyDBs
from sys.databases
where database_id < 4 --gets rid of the system DBs
and name not in (list of databases you don't want to read)
--assuming the NOT list is shorter than the DO list
April 29, 2009 at 5:08 am
Have a look into SSMS tools pack.
Among other very useful feautures it also offers the option to run a TSQL script against multiple database.
http://www.ssmstoolspack.com/Features.aspx
[font="Verdana"]Markus Bohse[/font]
April 29, 2009 at 7:55 pm
Brandie Tarvin (4/29/2009)
Cursor is your best option here, then. Use the below code to get your list of database names. Use the FETCH statement against this list. Plus, add a variable that uses substring to check the last two characters of your DB name. Use dynamic SQL for your update statement.You might even be able to use ForEachDB against the list. Anyway, that should work. It's not simulatenous, but it'll give you the flexibility you need as well as the automation.
Select name
into dbo.#MyDBs
from sys.databases
where database_id < 4 --gets rid of the system DBs
and name not in (list of databases you don't want to read)
--assuming the NOT list is shorter than the DO list
The scripts I have to run are huge: thousands of lines of code. With these options I have to watch the use of temp tables and make sure they are dropped each time. Will have to copy-paste the scripts into the cursor script each time... Too much manual work. Will have to accomodate the scripts for use with the cursor... Besides I can not change the scripts, because in production they will be run the normal way through Management Studion. I really can't use these options.
April 30, 2009 at 4:04 am
Roust_m (4/29/2009)
Too much manual work. ... I really can't use these options.
Well if everything we suggest is too much work, then I'm afraid we can't help you.
Sorry.
April 30, 2009 at 11:42 am
Redgate offers a tool called Multi Script that is very useful.
http://www.red-gate.com/products/SQL_Multi_Script/index.htm?gclid=CN-ChK-TmZoCFShRagod9RpU9g
Thanks.
BJC
May 1, 2009 at 9:53 am
Use a batch script at a console prompt running SQLCMD against each database. You can put the names of the databases in a text file. That is how we deploy all of our schema changes to 100s of databases during our tight maintenance window.
E.g.
sqlcmd.exe -S %SERVER% %CONNECT_INFO% -d %DATABASE% -e -b -w 500 -m-1 -I -i %INFILE% -o %LOGFILE%
May 1, 2009 at 9:59 am
Or even a PowerShell script to iterate through the list of databases and run the T-SQL script using SQLCMD. See:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply