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{


[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


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


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







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

1 Comment

  • Max says:

    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”

Leave a Reply

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