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"
1 Comment
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”