Executing a set of SQL Script Files (*.sql) on a Group of SQL Server Databases

This post is a continuation of my pervious articles:

In the last part, I showed how to extract attachments from the emails. In this part, we will see how to execute them in a batch on a number of SQL Server databases.

The Problem

To execute a batch of *.sql files on one or more SQL Server databases uninterrupted, without any human intervention. The sql files are assumed to be named in ascending order of their execution sequence.

The Solution

  1. Open SQL Server Management Studio.
  2. Copy and paste the following code in a new SQL file.
    
    USE Master
    GO
    
    -- To allow xp_cmdshell execution. 
    PRINT 'Enabling xp_cmdshell utility...'
    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    EXEC sp_configure 'xp_cmdshell', 1
    RECONFIGURE
    GO
    
    DECLARE @ScriptFilesDir VARCHAR(MAX), @Databases VARCHAR(MAX), @ServerName VARCHAR(MAX)
    
    -- ******************************************************************************************
    -- ***************************** USER CONFIGURABLE VARIABLES ********************************
    -- ******************************************************************************************
    SET @ScriptFilesDir		= 'C:\Temp\Scripts\'              --'
    SET @ServerName			= 'Your_SQLServer_Name'
    SET @Databases			= 'Database1, Database2, Database3, Database4, Database5, Database6'
    -- ******************************************************************************************
    
    DECLARE @FileList Table (FileNumber INT IDENTITY(1,1), FileName VARCHAR(255), Command VARCHAR(MAX))
    DECLARE @DatabaseList Table (DBNumber INT IDENTITY(1,1), DBName VARCHAR(MAX))
    DECLARE @Command VARCHAR(2048), @Command1 VARCHAR(2048) 
    
    SET NOCOUNT ON
    SET @Command = 'ECHO Running Scripts Started %DATE% %TIME% > ' + @ScriptFilesDir + 'Output.txt'
    EXEC xp_cmdshell @Command, NO_OUTPUT
    
    -- Get a list of *.sql files we want to execute on our databases 
    SET @Command = 'DIR /B /O:-N ' + @ScriptFilesDir + '*.sql'
    INSERT INTO @FileList (FileName) EXEC xp_cmdshell @Command 
    DELETE FROM @FileList WHERE FileName IS NULL OR FileName NOT LIKE '%.sql'
    UPDATE @FileList SET Command = 'sqlcmd -S ' + @ServerName 
    							 + ' -d %DATABASE% -i "' + @ScriptFilesDir + FileName 
    							 + '" >> ' + @ScriptFilesDir + 'Output.txt'
    
    -- Create a list of databases to work upon 
    DECLARE @BeginPos INT, @NextPos INT, @SubStrLen INT
    SELECT @BeginPos = 0, @NextPos = 1
    WHILE @NextPos > 0
    BEGIN
    	SET @NextPos = CHARINDEX(',', @Databases, @BeginPos + 1)
    	SET @SubStrLen = CASE WHEN @NextPos > 0 THEN @NextPos ELSE LEN(@Databases) + 1 END - @BeginPos - 1
    	INSERT INTO @DatabaseList (DBName) VALUES (LTRIM(RTRIM((SUBSTRING(@Databases , @BeginPos + 1, @SubStrLen)))))
    	SET @BeginPos = @NextPos
    END
    
    -- Run the script files one by one on each of the specified databases 
    PRINT ''
    PRINT 'Running Scripts...'
    DECLARE @DBName VARCHAR(255), @DbNumber INT, @FileName VARCHAR(255), @FileNumber INT
    SELECT @FileNumber = MAX(FileNumber) FROM @FileList 
    WHILE (@FileNumber > 0)
    BEGIN
    	SELECT @FileNumber = @FileNumber - 1, @FileName = FileName, @Command = Command FROM @FileList WHERE FileNumber = @FileNumber 
    	RAISERROR ('    Executing file %s',0, 1, @FileName) WITH NOWAIT
    	SET @Command1 = 'ECHO ' + @FileName + ': >> ' + @ScriptFilesDir + 'Output.txt'
    	EXEC xp_cmdshell @Command1, NO_OUTPUT
    
    	SELECT @DbNumber = MAX(DbNumber) FROM @DatabaseList
    	WHILE @DbNumber > 0 
    	BEGIN
    		SELECT @DbNumber = @DbNumber - 1, @DBName = DBName FROM @DatabaseList WHERE DbNumber = @DbNumber
    		--RAISERROR ('        %s',0, 1, @DBName) WITH NOWAIT
    		SET @Command1 = REPLACE(@Command, '%DATABASE%', @DBName)
    		EXEC xp_cmdshell @Command1, NO_OUTPUT
    	END
    END
    PRINT 'Running Scripts Complete'
    PRINT ''
    
    -- Show the program output & errors on the screen. 
    DECLARE @OutputTable Table (Output VARCHAR(MAX))
    SET @Command = 'type "' + @ScriptFilesDir + 'Output.txt"'
    INSERT INTO @OutputTable EXEC xp_cmdshell @Command 
    SELECT * FROM @OutputTable WHERE Output IS NOT NULL
    SET @Command = 'ECHO Running Scripts Completed %DATE% %TIME% >> ' + @ScriptFilesDir + 'Output.txt'
    EXEC xp_cmdshell @Command, NO_OUTPUT
    GO
    
    -- To disable xp_cmdshell again once we are thru with our work. 
    PRINT 'Disabling xp_cmdshell utility...'
    EXEC sp_configure 'xp_cmdshell', 0
    RECONFIGURE
    EXEC sp_configure 'show advanced options', 0
    RECONFIGURE
    GO
    
    PRINT ''
    PRINT 'Check the Output.txt file for intermediate output and errors.'
    GO
    
    
  3. Modify the code and set the variables appropriately.

    @ScriptFilesDir: The folder path where your script files are located.

    @ServerName: The name of your SQL Server instance.

    @Databases: A comma separated list of databases, on which the scripts should run.

  4. Save this file somewhere on your disk (for future use).
  5. Press F5 key or click the Execute button to run the script.
  6. Switch over to the Messages tab to see the current status of the script run. Wait for the script to finish execution.
  7. Once the script has finished execution, you can view the result of execution and any errors in the Output.txt file. This is created in the @ScriptFilesDir folder

Enjoy! :)

Further Developments

This script was meant for my personal use. So I didn’t care much about packaging it nicely/properly, maybe into a Stored Procedure etc. I’m comfortable opening this SQL script file everytime I need it, and click the Execute button.

However if you want to implement the solution on a professional level (e.g. within your organization etc.), then you may consider modifying the script and converting it into Stored Procedures etc. or develop a front-end application for this.

 

Previous Parts:

  1. Introduction
  2. Extracting Attachments from Selected Emails
About these ads

21 Responses to “Executing a set of SQL Script Files (*.sql) on a Group of SQL Server Databases”

  1. Joan A. Martin Says:

    I truly wanted to write down a quick remark to thank you for these pleasant secrets you are showing at this website. My time-consuming internet lookup has at the end been rewarded with really good know-how to go over with my contacts. I ‘d express that we readers actually are undoubtedly blessed to dwell in a fantastic community with many outstanding individuals with insightful concepts. I feel rather grateful to have used the web site and look forward to tons of more cool times reading here. Thank you once more for all the details.

  2. Kyle Naidoo Says:

    how do you display the actual results of this query to the screen?

    • pradeep1210 Says:

      When the query is running, if you switch over to the “Messages” tab, you can see the current status.
      When the query has finished executing, the result is displayed in the “Results” tab.
      The results are also saved to a file on disk. You can open the “C:\Temp\Scripts\Output.txt” file (if you didn’t change the @ScriptFilesDir variable in the script) to view the results.

      Please have a look at the video link in the first part of this article. It should make the things clear to you.

  3. vj Says:

    Very good script. Saved me lots of time. Thanks!!!

  4. Florida Says:

    Hello! Someone in my Myspace group shared this website with us so I came to check it out.
    I’m definitely loving the information. I’m book-marking and will
    be tweeting this to my followers! Outstanding blog and amazing design.

  5. Alexander Says:

    This is my first time go to see at here and i am genuinely pleassant to read everthing at
    alone place.

  6. Mike Says:

    Seems to be a nice exercise, but the following works without writing any code or opening your servers to a security risk:

    http://msdn.microsoft.com/en-us/library/bb964743(v=sql.100).aspx

    Documented risk:

    http://msdn.microsoft.com/en-us/library/ms175046.aspx

  7. Whitney Says:

    You actually make it seem so easy with your presentation but I find this
    matter to be really one thing that I feel I would never understand.
    It sort of feels too complex and very broad for
    me. I am taking a look ahead to your subsequent put up, I will try to get the cling of it!

  8. Mamie Says:

    If you would like to improve your familiarity just keep visiting this website
    and be updated with the most up-to-date news posted here.

  9. Vikram Patil Says:

    Hi Pradeep,

    Really appreciate your work and help, please help out for, i have executed the script i am getting the output as ‘Access is Denied’.

    • pradeep1210 Says:

      Check that the user with which you logged in has adequate privileges. I was logged in as “sa” user while building and testing the script, so didn’t have any problems.

      • Vikram Patil Says:

        I have also tried with “sa” user, but same result ‘Access is Denied’.
        And is that necessary to create proxy account to execute this script.?

      • pradeep1210 Says:

        I can think of a few things, though not sure if these would be useful.
        1. The physical folder specified by @ScriptFilesDir (‘C:\Temp\Scripts\’ in my case) may not have adequate read/write permissions.
        2. Possibly your sql files are not on the server drive. The drive specified @ScriptFilesDir path is the harddisk drive on SQL Server machine.

        Can you experiment around and see which line of code is failing? That might give more clues. You can do that by selecting text from beginning to a few lines and pressing F5 key. If that works, then extend the selection to a few more lines and repeat until you can pinpoint the failing line of code.

      • Vikram Patil Says:

        Hi Pradeep,

        — Show the program output & errors on the screen.
        1 DECLARE @OutputTable Table (Output VARCHAR(MAX))
        2 SET @Command = ‘type “‘ + @ScriptFilesDir + ‘Output.txt”‘
        3 INSERT INTO @OutputTable EXEC xp_cmdshell @Command
        4 SELECT * FROM @OutputTable WHERE Output IS NOT NULL
        5 SET @Command = ‘ECHO Running Scripts Completed %DATE% %TIME% >> ‘ + @ScriptFilesDir + ‘Output.txt’
        6 EXEC xp_cmdshell @Command, NO_OUTPUT

        Its at line number 4 in above set of line giving me ‘Access is denied’. Except that line all other lines are executed….

      • Vikram Patil Says:

        In this page or blog above that is @ line number 77, its showing ‘Access is Denied’.

      • pradeep1210 Says:

        There is some problem with the folder to which @ScriptFilesDir points to.

        Try the following:
        1. Ensure that the path set in @ScriptFilesDir ends with a “\”. e.g. It should be ‘C:\Temp\Scripts\’ but not ‘C:\Temp\Scripts’
        2. Ensure that the folder to which @ScriptFilesDir points has write permissions. This is required to write the output log to a file.

      • Vikram Patil Says:

        Thanks for patience and reply, yes path is perfect and permission is full control (i.e read , write etc) all permissions.

      • Vikram Patil Says:

        Hi Pradeep,

        Thanks for your suggestions, finally done, set folder permissions to everyone it worked.

  10. Hatem Says:

    Excellent work.

  11. Updating Patch Scripts to DB | mysysnetsoftwarediv Says:

    […] Executing a set of SQL Script Files (*.sql) on a Group of SQL Server Databases […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: