There are many instances when using the SSMS GUI is not practical. One of these is when backing up or restoring large numbers of databases such as when doing server migrations and/or consolidations. I wrote a simple script years ago that only read from the sys.databases table but that proved unusable the first time I ran into a database that had multiple data files. The script below joins sys.databases with sys.master_files and the resulting script will generate the proper restore command no matter how many mdf, ndf or ldf files are in the database. You will likely have to modify the code to adjust for your backup location or for different preferences regarding command line arguments. This will run under SQL 2005 or SQL 2008.
-- Purpose: Generate 'restore database' statements
-- Instructions: Edit 'where' clause and/or backup path information in variables as
-- needed. Run script to generate sql, then edit/modify as needed and run selected sql statements.
--
set nocount ON
DECLARE @RestoreDB VARCHAR (8000)
Declare @Move varchar (8000)
Declare @MoveWithoutComma varchar (8000)
Declare @Go varchar (8000)
Declare @firsttime varchar (5) -- = 'True'
declare @sd_name varchar(255)
declare @smf_name varchar(255)
declare @smf_physical_name varchar (255)
declare @hold_sd_name varchar (255)
select @firsttime = 'True'
DECLARE Database_cursor CURSOR FOR
SELECT sd.name,smf.name, smf.physical_name
FROM sys.master_files AS smf inner join sys.databases AS sd
on smf.database_id = sd.database_id
-- Edit where clause as desired
where sd.name not in ('master', 'tempdb', 'msdb', 'model')
ORDER BY sd.name
OPEN Database_cursor
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
Select @hold_sd_name = @sd_name
WHILE @@FETCH_STATUS = 0
Begin
if @firsttime = 'True'
Begin
-- Edit backup path as needed
select @RestoreDB = 'RESTORE DATABASE [' + @sd_name + ']' + char(13) + char(10) + 'FROM DISK = ''J:\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'
+ @sd_name + '.bak''' + char(13) + char(10) + 'WITH MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''', replace ,' + 'stats = 10' + char(13) + char(10)
print @RestoreDB
select @Go = 'GO' + char(13) + char(10)
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
select @hold_sd_name = @sd_name
else select @firsttime = 'False'
end
else
Begin
-- Move with comma ','
select @Move = 'MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''','
-- Last 'Move' without comma ','
select @MoveWithoutComma = 'MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''''
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
Begin
print @MoveWithoutComma
print @Go
select @hold_sd_name = @sd_name
select @firsttime = 'True'
end
Else print @Move
end
END
CLOSE Database_cursor
DEALLOCATE Database_cursor
GO
Friday, March 15, 2013
Thursday, March 14, 2013
Script to Generate Registered Servers
I've always been a fan of Registered Servers in SSMS. However, a few years ago I took a job with a large company that had several hundred servers distributed across DEV, Test and Prod environments. The existing DBA's had made an attempt to organize the servers in a Central Management Server but it quickly became out of date as servers came and went on almost a daily basis and maintenance quickly became a nightmare.
The script below utilizes metadata stored in a maintenance database of some sort. You have to figure that out. I've included a sample table and view. The particular table I used stores the server name, instance name, and environment among other things. Several cursors run consecutively to build the various parts of the XML script. If you have a place where you store this kind of metadata about your SQL Servers, you should be able to modify the cursors (there are 4 of them) and variables.
I've modified the script I posted back in 2013 and it currently works for SQL Server 2016. It should still work for previous versions as well as I have not needed to touch the XML that is generated.
Here is the table, view, main script and instructions.
/****** Object: Table [dbo].[SQL_Metadata_Table] Script Date: 4/9/2017 10:40:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Here's a sample metadata table and view design. The view must also be created to use the "Registered Servers"
script as written. Obviously, you can make modifications to suit your own needs. The script as I've
written it only requires an "Environment" and "Server_Instance_Name" column but you may want to track
more data elements than this.j
To run the script, you must have created the table and view and entered some SQL Instance data.
The output from the script should be saved as a '.regsrvr' file. You then 'import' the file into
Management Studio. Right-click 'Local Server Groups', 'Tasks' and 'Import'. Choose the file you
just saved and click 'OK'. File should import successfully and your registered servers appear.
*/
CREATE TABLE [dbo].[SQL_Metadata_Table](
[Server_Name] [varchar](255) NOT NULL,
[Instance_Name] [nvarchar](255) NULL,
[Server Desc] [nvarchar](255) NULL,
[Environment] [nvarchar](255) NULL, -- i.e. DEV, TEST, PROD
[Primary_DBA] [int] NULL, -- Numeric value for each DBA i.e. Dave = 1, Tom = 2, Mary = 3
[Secondary_DBA] [int] NULL, -- Same as above
[Status] [smallint] NULL, -- Active = 1
[Server_Comment] [varchar](255) NULL)
go
CREATE VIEW [V_SQL_Metadata_Table]
AS
SELECT [Environment],
Server_Instance_Name = CASE
WHEN [Instance_Name] IS NULL THEN [Server_Name]
ELSE [Server_Name] + '\' + [Instance_Name]
END
FROM [dbo].[SQL_Metadata_Table];
go
-- Script follows
declare @Environment varchar (50),
@Server varchar (50),
@Header varchar (8000),
@MasterServerName varchar (50),
@SubGrpLine1 varchar (8000),
@SubGrpLine2 varchar (8000),
@SubGrpLine3Header varchar (8000),
@SubGrpLine3 varchar (8000),
@SubGrpLine3Footer varchar (8000),
@SubGrpLine4Header varchar (8000),
@SubGrpLine4 varchar (8000),
@SubGrpLine4Footer varchar (8000),
@SubGrpLineFooter varchar (8000),
@EntireLine varchar(8000),
@Footer varchar (8000)
-- Set Master Server Name
select @MasterServerName = 'Insert your server/instance name here'
--
-- Generate XML for generic header
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
select @Header = '<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
<identity>
<name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
<baseURI>http://documentcollection/</baseURI>
</identity>
<xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/system/schema/RegisteredServers</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="ServerGroup">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="RegisteredServer">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
<instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</sml:Uri>
</sfc:Reference>
</sfc:Collection>
</RegisteredServers:RegisteredServers>
<RegisteredServers:ServerGroups>
<sfc:Collection>'
print @Header
--
-- Generate XML to create 1st Section for SQL Server Groups
--
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine1 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine1
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLineFooter = '</sfc:Collection>
</RegisteredServers:ServerGroups>
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @MasterServerName + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @MasterServerName + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLineFooter
--
-- Generate XML to create 2nd Section for SQL Server Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine2 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Environment + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine2
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML to create Section for SQL Server Servers within Groups
--
-- Section Header
select @SubGrpLine3Header = '<document>
<docinfo>
<aliases>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>'
Print @SubGrpLine3Header
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine3 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '/RegisteredServer/' + @Server + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine3
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLine3Footer = '</sfc:Collection>
</RegisteredServers:RegisteredServers>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine3Footer
--
-- Generate XML to create 2nd Section for SQL Server Servers within Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine4 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '/RegisteredServer/' + @Server + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Server + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @Server + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @Server + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLine4
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML for final footer
--
select @Footer = ' </instances>
</RegisteredServers:bufferData>
</xs:schema>
</data>
</document>
</definitions>
</xs:bufferSchema>
</model>'
print @Footer
GO
The script below utilizes metadata stored in a maintenance database of some sort. You have to figure that out. I've included a sample table and view. The particular table I used stores the server name, instance name, and environment among other things. Several cursors run consecutively to build the various parts of the XML script. If you have a place where you store this kind of metadata about your SQL Servers, you should be able to modify the cursors (there are 4 of them) and variables.
I've modified the script I posted back in 2013 and it currently works for SQL Server 2016. It should still work for previous versions as well as I have not needed to touch the XML that is generated.
Here is the table, view, main script and instructions.
/****** Object: Table [dbo].[SQL_Metadata_Table] Script Date: 4/9/2017 10:40:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Here's a sample metadata table and view design. The view must also be created to use the "Registered Servers"
script as written. Obviously, you can make modifications to suit your own needs. The script as I've
written it only requires an "Environment" and "Server_Instance_Name" column but you may want to track
more data elements than this.j
To run the script, you must have created the table and view and entered some SQL Instance data.
The output from the script should be saved as a '.regsrvr' file. You then 'import' the file into
Management Studio. Right-click 'Local Server Groups', 'Tasks' and 'Import'. Choose the file you
just saved and click 'OK'. File should import successfully and your registered servers appear.
*/
CREATE TABLE [dbo].[SQL_Metadata_Table](
[Server_Name] [varchar](255) NOT NULL,
[Instance_Name] [nvarchar](255) NULL,
[Server Desc] [nvarchar](255) NULL,
[Environment] [nvarchar](255) NULL, -- i.e. DEV, TEST, PROD
[Primary_DBA] [int] NULL, -- Numeric value for each DBA i.e. Dave = 1, Tom = 2, Mary = 3
[Secondary_DBA] [int] NULL, -- Same as above
[Status] [smallint] NULL, -- Active = 1
[Server_Comment] [varchar](255) NULL)
go
CREATE VIEW [V_SQL_Metadata_Table]
AS
SELECT [Environment],
Server_Instance_Name = CASE
WHEN [Instance_Name] IS NULL THEN [Server_Name]
ELSE [Server_Name] + '\' + [Instance_Name]
END
FROM [dbo].[SQL_Metadata_Table];
go
-- Script follows
declare @Environment varchar (50),
@Server varchar (50),
@Header varchar (8000),
@MasterServerName varchar (50),
@SubGrpLine1 varchar (8000),
@SubGrpLine2 varchar (8000),
@SubGrpLine3Header varchar (8000),
@SubGrpLine3 varchar (8000),
@SubGrpLine3Footer varchar (8000),
@SubGrpLine4Header varchar (8000),
@SubGrpLine4 varchar (8000),
@SubGrpLine4Footer varchar (8000),
@SubGrpLineFooter varchar (8000),
@EntireLine varchar(8000),
@Footer varchar (8000)
-- Set Master Server Name
select @MasterServerName = 'Insert your server/instance name here'
--
-- Generate XML for generic header
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
select @Header = '<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
<identity>
<name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
<baseURI>http://documentcollection/</baseURI>
</identity>
<xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/system/schema/RegisteredServers</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="ServerGroup">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="RegisteredServer">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
<instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</sml:Uri>
</sfc:Reference>
</sfc:Collection>
</RegisteredServers:RegisteredServers>
<RegisteredServers:ServerGroups>
<sfc:Collection>'
print @Header
--
-- Generate XML to create 1st Section for SQL Server Groups
--
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine1 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine1
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLineFooter = '</sfc:Collection>
</RegisteredServers:ServerGroups>
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @MasterServerName + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @MasterServerName + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLineFooter
--
-- Generate XML to create 2nd Section for SQL Server Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine2 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Environment + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine2
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML to create Section for SQL Server Servers within Groups
--
-- Section Header
select @SubGrpLine3Header = '<document>
<docinfo>
<aliases>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>'
Print @SubGrpLine3Header
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine3 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '/RegisteredServer/' + @Server + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine3
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLine3Footer = '</sfc:Collection>
</RegisteredServers:RegisteredServers>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine3Footer
--
-- Generate XML to create 2nd Section for SQL Server Servers within Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine4 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '/RegisteredServer/' + @Server + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Server + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @Server + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @Server + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLine4
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML for final footer
--
select @Footer = ' </instances>
</RegisteredServers:bufferData>
</xs:schema>
</data>
</document>
</definitions>
</xs:bufferSchema>
</model>'
print @Footer
GO
Subscribe to:
Posts (Atom)