SQL Server: How to attach all databases from specific folder

I recently had to change SQL Server instance collation using the steps I described in my earlier post. After this operation dozen of databases which were hosted on that instance become detached and I was able to see only set of instance’s system DBs. So I had to look for an option to attach all DB from folder via script as doing this manually for 20 or so databases a bit too much of an effort to me 🙂

There is “Attach all the databases in one folder” script available in TechNet Script Center which was able to solve this problem for me. All you need it pass DatabaseDir parameter to the function specifying folder with your DBs:

Attach-AllDatabasesInDir -DatabaseDir "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA"

Just in case I provide PS code of this function below in case you don’t want to grab the same from TechNet Script Center. All credit goes to Ivan Josipovic.

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

# 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 Server"

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"
Please follow and like us:

1 thought on “SQL Server: How to attach all databases from specific folder

  1. Max

    Helo, when I run this script, it is not work.
    Write this red: “File was not able to be read. It is most likely already mounted or in use by another application”

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *