Post reply

Looking to find Express instance of SQL

  • July 3, 2011 at 9:00 pm

    #243473

    Hi.

    I found this script to find all SQL instance on your network:

    function get-SqlServerList {[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()}

    get-SqlServerList

    And I also played around with a script that backs up all DBs on my server

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

    $hostname = hostname

    $ServerName = $hostname + "\express"

    $ServerSMO = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerName

    $backupDirectory = $server.Settings.BackupDirectory

    $BackupDate = get-date -format yyyyMMdd_HHmmss

    $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)

    }

    }

    What I need help with is finding out the instance name on a server. Even better would be to find out what express instances I have the machine.

    The goal here is to create a generic script I can load onto a server that backs up SQL Express instances. I want to use this to create a scheduled task.

    I have about 30 servers or so and some have express on them. I want to try not to customize this script for each instance\server.

    Any ideas would be appreciated.

  • July 5, 2011 at 4:28 pm

    #1348652

    get-SqlServerList returns the instance name as part of the results. All I see you need to do is connect to each instance returned by that call and select the Edition SERVERPROPERTY to see if it's an Express instance and go from there.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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