script multi instance SQL server

Question

Post reply

script multi instance SQL server

abdalah.mehdoini

Ten Centuries

Points: 1001

June 13, 2019 at 9:22 am

#3653118

Hello everyone

Who has an idea how can I modify this script to run it on multiple instances Currently I run the script on a single instance

$ServerName = "*****"
$BackupDirectory = "F:\Backups\"
$BackupDate = get-date -format yyyyMMdd_HHmmss 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$ServerSMO = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerName 
$DatabaseList = $ServerSMO.Databases


foreach ($Database in $DatabaseList)
{
if($Database.Name -ne "tempdb") 
{
$DatabaseName = $Database.Name

$DatabaseBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$DatabaseBackup.Action = "Database"
$DatabaseBackup.Database = $DatabaseName
$DatabaseBackup.Devices.AddDevice($BackupDirectory + "\" + $DatabaseName + "_" + $BackupDate + ".BAK", "File")
$DatabaseBackup.SqlBackup($ServerSMO)
}
}

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

you'll just need a loop that iterates per server

the question is where the list of servers comes from, here i have an example of populating form a query, but you could hardcode the lsit as well.

$CMSSource = 'MyCMSServer\InstanceName'
$cmsQuery = @"
SELECT TOP 20
    srvz.server_name AS ServerName,[grpz].[name] As GroupName
FROM msdb.dbo.sysmanagement_shared_registered_servers srvz 
    INNER JOIN msdb.dbo.[sysmanagement_shared_server_groups] grpz
    ON srvz.[server_group_id] = [grpz].[server_group_id] 
    WHERE [grpz].[name] LIKE 'Production%'
    
"@
###################################################################################################
## The Work
###################################################################################################
$AllServers =  Invoke-SQLCMD -Query $cmsQuery -ServerInstance $CMSSource
#$AllServers = @('WIN-SDF567','WIN-HJK345','WIN-ASD234','WIN-CVB890')
foreach ($Server in $AllServers)
{
  ##your per server work goes here:
  
  #from the query, you have to use $Server.ServerName
  #but from an array, it is just $Server
  
} ##End foreach ($Server in $AllServers)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!