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:
error0

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 *