script multi instance SQL server

  • abdalah.mehdoini

    SSC Eights!

    Points: 844

    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)
    }
    }
  • Lowell

    SSC Guru

    Points: 323377

    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!

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

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