Posted on:
Categories: SQL
Description:

Issue:
Due to a disk configuration issue we had to remount ~200 databases to our Staging SQL Server. This proved to be a very tedious task using the SQL Server Management Studio. I have read that Microsoft has added significant PowerShell improvements to SQL over the years but I have yet to play with it. This proved to be a perfect time.

Interfacing with SQL proved to be very easy thanks to PowerShell.

Using the following two lines of code you will be able to get to the SQL Server object. From here the possibilities are endless. Follow the link below for the Class Documentation.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx

#Load the SQL Assembly

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null

#Connect to the local SQL Server Instance, change the (local) parameter if needed

$server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")

 

Solution:


http://gallery.technet.microsoft.com/scriptcenter/Attach-all-the-databases-ace9ed34

 

#------------------------------------------------------------------------------------------------------

# Name:         Attach-AllDatabasesInDir

# Description: This script will attach all the databases in a directory to the local SQL Instance

# Usage:        Run the function with the -DatabaseDir parameter

# By:           Ivan Josipovic, softlanding.ca

#------------------------------------------------------------------------------------------------------

#Do not modify below here

 

function Attach-AllDatabasesInDir{

param(

[string]$DatabaseDir = $(throw "-DatabaseDir `"C:\ExampleDir`" is required.")

)

#Load the SQL Assembly

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null

#Connect to the local SQL Server Instance, change the (local) parameter if needed

$server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")

if ($server -eq $null){

    Write-host -foreground red "Unable to connect to the SQL Servers"

    return -1

}

$items = get-childitem $DatabaseDir *.mdf

 

foreach ($item in $items){

    [bool]$ErrorExists = $false

    $Item.name

 

    try {

        $DBName = $server.DetachedDatabaseInfo($($item.fullname)).rows[0].value

    }

    catch {

        Write-host -foregroundcolor red "File was not able to be read. It is most likely already mounted or in use by another application"

        $ErrorExists = $true

    }

 

    if ($ErrorExists -eq $false){

        foreach ($db in $server.databases){

            if ($db.name.Equals($DBName)){

                write-host -foreground red "This database already exists on the server"

                $ErrorExists = $true

            }

        }

        if ($ErrorExists -eq $false){

            $DbLocation = new-object System.Collections.Specialized.StringCollection

            $DbLocation.Add($item.fullname)

            $attach = $server.AttachDatabase($DBName, $DbLocation)

        }

    }

}

return

}

#------------------------------------------------------------------------------------------------------

 

Attach-AllDatabasesInDir -DatabaseDir "D:\Data\SQLDat\MOSSData"