Attaching Database Using SMO & Powershell

Steve Jones wrote a great article on using this automation titled The Demo Setup-Attaching Databases with Powershell. I threw together a completed script and modified it for my functionality here. MSDN documentation on the functionality is located here Server.AttachDatabase Method (String, StringCollection, String, AttachOptions)I see some definitive room for improvement with some future work on this to display percentage complete and so on, but did not implement at this time.

For the nested error handling I found a great example of handling the error output from: Aggregated Intelligence: Powershell & SMO-Copy and attach database. If you don’t utilize the logic to handle nested errors your powershell error messages will be generic. This handling of nested error property is a must to be able to debug any errors you run into. http://blog.aggregatedintelligence.com/2012/02/powershell-smocopy-and-attach-database.html

If you want to see some great example on powershell scripting restores with progress complete and more I recommend taking a look at this post which had a very detailed powershell script example. SharePoint Script - Restoring a Content Database

Reattach Database Files to Server with Powershell

GitHub

[cmdletbinding()]
param(
  [Parameter(Mandatory =  $false)][string]$instance                   = 'MyFancyServer'
  ,[Parameter(Mandatory = $false)][string]$DesiredDatabaseName        = 'AdventureWaxWorks'
  ,[Parameter(Mandatory = $false)][string]$DatabaseFileName           = 'AdventureWaxWorksFile' #this does need data file + ldf file to have same name + _log on log file
)


$VerbosePreference = 'Continue'

clear-host
Import-Module "sqlps" -DisableNameChecking -verbose:$false

$server = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance
Set-Variable -Name DataDirectory -Value ($server.Settings.DefaultFile)         -Verbose:$true    -Force
Set-Variable -Name MdfFile -Value ([io.path]::Combine($DataDirectory,"$($DatabaseFileName).mdf")) -Verbose -Force
Set-Variable -Name LdfFile -Value ([io.path]::Combine($DataDirectory,"$($DatabaseFileName)_log.ldf" )) -Verbose -Force

$dbfiles = New-Object System.Collections.Specialized.StringCollection
$dbfiles.Add($MdfFile) 
$dbfiles.Add($LdfFile)
write-host "Database Files to Attach"
$dbfiles | format-table 
try 
{ 
  $server.AttachDatabase($DesiredDatabaseName, $dbfiles, "sa", "None")

}

catch 
{ 
    Write-Host $_.Exception; 
    if ($Error.Count -gt 0) 
    { 
        Write-Host "Error Information" -BackgroundColor Red; 
        $error[0] | fl -force ; 
    } 
}

#$dbfiles.Clear() #for if you want to modify to run through various databases in a loop you can use this
return