All geek questions in one place

SQL Server, execute batch T-SQL script in multiple databases

Our instance of SQL Server 2000 contains several databases that are all the same, one for each of our clients. When it is time to update them, we use Red SQL SQL Compare to create a script migration between the development database and a copy of the current database state of all client databases.

SQL Compare generates a script that is transactional; if one step fails, the script rolls back everything. But currently, our system uses a method that breaks the script into packet delimiters (the GO statement ), and then runs each command separately, which destroys all transactional materials. GO statement not supported when querying software database (in classic ASP)

I want to know how I can run this script (save transactions) in all these databases (e.g. 250 DB), programmatically or manually in a tool? In the Query Analyzer, we need to select each database and click Run , which is quite large for the number of databases we have.

+3
source share
5 answers

I am using sqlcmd external command line tool . I have the same situation on the server on which I work.

I have a script in * .sql file and a list of databases in the second file. I have a small * .bat script that iterates over all the databases and runs the script using the sqlcmd command .

In more detail I like this:

  • DB.ini file with all the databases on which I want to deploy my script
  • sql / directory where all scripts are stored
  • runIt.bat - a script that deploys scripts

The command line looks more or less:

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -d <database_name> -T

SQL Server 2000 osql

UPDATE

Red Gate SQL Multi script, , . SQL 2000 2008 R2 , .

+2

SSMS SQL 2005 2008, SSMS Tool

+5

7 , :

TAKODEPLOY

:

  • . .
  • , . : .
  • Script editor (Avalon Text, monodevelop)
  • , .
  • "" GO.
  • .
  • , ( PRINT !).
  • .
  • .

: https://github.com/andreujuanc/TakoDeploy

+1

, , GO .

0

, script SQL Compare, . , , .

( ), , , prod, , prod. - ( - , , ). , , UDF, proc. - - , , , , , .

0

Source: https://habr.com/ru/post/1718312/

More articles:


All Articles