Running SQL scripts across multiple servers

Question

Background:

I have a Test Server (SQL Server 2008 R2) that contains a DB in which I store information about other DBs from my Production Servers (Mix of SQL Server 2008 R2 and 2012). I have multiple scripts that I run monthly which collects information, such as number of VLFs, from each DB on a server. What I have been doing is running the scripts on each of my Production Servers, copying the results into a .txt file, and importing the .txt file into the DB on my Test Server.

Problem:

How can automate my scripts to run on every Production server without me logging on to each Production Server and running each script?
How can I insert the collected information into my Test Server DB without having to copy/paste?

Sample Script:

SET NOCOUNT ON;

CREATE TABLE #to
(
  ServerName varchar(20),
  CaptureDate DATETIME,
  DBName SYSNAME,
  FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), 
  SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
  (
    ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '    
    FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + '
  INSERT #ti EXEC ' + QUOTENAME(name) 
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT INTO 
        #to(DBName, FileCount) SELECT ''' + name + ''', COUNT(*) FROM #ti;
  TRUNCATE TABLE #ti;'
FROM sys.databases;

EXEC sp_executesql @sql;

SELECT @@ServerName,GETDATE(),DBName,FileCount 

FROM #to

ORDER BY DBName

DROP TABLE #to;

Any help would be greatly appreciated.

dbsql is the command line tool to access SQL Server. You can run it with the '-i' flag to call a script name. — Mike Gardner
– Mike Gardner, Commented May 1, 2013 at 15:24

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

Create a linked server on each of your production SQL Servers to the test database

This link provides more info on linked servers http://msdn.microsoft.com/en-us/library/ff772782.aspx
Create a SQL Server job which runs a SQL script which inserts data into the table on the test server (via the linked server)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 1, 2013 at 15:25

James Farrer

834 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Hurst Over a year ago

Thank you for your response... When I create the job script do I need to work with the "Target Multiple Servers" option from the Job Properties? I'm missing how to relate the Test Server's job script to the Production Servers.

Collectives™ on Stack Overflow

Running SQL scripts across multiple servers

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related