How to Attach/Detach SQL Server Databases with PowerShell

The ability to easily attach/detach SQL databases when testing is incredibly valuable. Whilst working on a procedure to restore corrupted data in a database I had to create some scripts to automate attaching and detaching of SQL Server databases.

I thought I would share the scripts as they can be easily added to any existing scripts to help automate attaching database clones based on Pure snapshots.

First thing to do is create a AttachDatabasesConfig.xml file to list the databases and their corresponding data/log files to be attached. Here is an example: –

&lt;?xml version="1.0" encoding="utf-8"?&gt;
&lt;SQL&gt;
&lt;Database&gt;
&lt;name&gt;TESTDATABASE1&lt;/name&gt;
&lt;data&gt;F:\data\TESTDATABASE1.MDF&lt;/data&gt;
&lt;data&gt;F:\data\TESTDATABASE1_2.NDF&lt;/data&gt;
&lt;data&gt;F:\data\TESTDATABASE1_3.NDF&lt;/data&gt;
&lt;data&gt;F:\data\TESTDATABASE1_4.NDF&lt;/data&gt;
&lt;log&gt;F:\log\TESTDATABASE1_log.LDF&lt;/log&gt;
&lt;/Database&gt;
&lt;Database&gt;
&lt;name&gt;TESTDATABASE2&lt;/name&gt;
&lt;data&gt;F:\data\TESTDATABASE2.MDF&lt;/data&gt;
&lt;data&gt;F:\data\TESTDATABASE2.NDF&lt;/data&gt;
&lt;log&gt;F:\log\TESTDATABASE2_log.LDF&lt;/log&gt;
&lt;/Database&gt;
&lt;/SQL&gt;

<?xml version="1.0" encoding="utf-8"?>

<SQL>

<name>TESTDATABASE1</name>

<data>F:\data\TESTDATABASE1.MDF</data>

<data>F:\data\TESTDATABASE1_2.NDF</data>

<data>F:\data\TESTDATABASE1_3.NDF</data>

<data>F:\data\TESTDATABASE1_4.NDF</data>

<log>F:\log\TESTDATABASE1_log.LDF</log>

</Database>

<name>TESTDATABASE2</name>

<data>F:\data\TESTDATABASE2.MDF</data>

<data>F:\data\TESTDATABASE2.NDF</data>

<log>F:\log\TESTDATABASE2_log.LDF</log>

</Database>

</SQL>

How to Detach Server Databases

Detach-SQLDatabases.ps1; this is the same script as in the Attach-SQLDatabases.ps1 except for the $attachSQLCMD statement using sp_detach.

#
# Load configuration XML file.
#
[xml]$databases = Get-Content "\\MSFT-INFRA-01\Script Library\AttachDatabasesConfig.xml"

#
# Get SQL Server database (MDF/LDF).
#
ForEach ($database in $databases.SQL.Databases) {
    $mdfFilename = $database.MDF
    $ldfFilename = $database.LDF
    $DBName = $database.DB_Name

    #
    # Detach SQL Server database
    #
    Add-PSSnapin SqlServerCmdletSnapin* -ErrorAction SilentlyContinue
    If (!$?) {Import-Module SQLPS -WarningAction SilentlyContinue}
    If (!$?) {"Error loading Microsoft SQL Server PowerShell module. Please check if it is installed."; Exit}
$attachSQLCMD = @"
USE [master]
GO
sp_detach_db $DBName
GO
"@
    Invoke-Sqlcmd $attachSQLCMD -QueryTimeout 3600 -ServerInstance 'MSFT-DEMOBOX-01\PURE1'

}

# Load configuration XML file.

[xml]$databases = Get-Content "\\MSFT-INFRA-01\Script Library\AttachDatabasesConfig.xml"

# Get SQL Server database (MDF/LDF).

ForEach ($database in $databases.SQL.Databases) {

$mdfFilename = $database.MDF

$ldfFilename = $database.LDF

$DBName = $database.DB_Name

# Detach SQL Server database

Add-PSSnapin SqlServerCmdletSnapin* -ErrorAction SilentlyContinue

If (!$?) {Import-Module SQLPS -WarningAction SilentlyContinue}

If (!$?) {"Error loading Microsoft SQL Server PowerShell module. Please check if it is installed."; Exit}

$attachSQLCMD = @"

USE [master]

sp_detach_db $DBName

Invoke-Sqlcmd $attachSQLCMD -QueryTimeout 3600 -ServerInstance 'MSFT-DEMOBOX-01\PURE1'

}

How to attach Server Databases

Once the XML configuration file is created, the following script will retrieve the information in the file and attach the databases to a target instance: –

# import sqlserver powershell module
Import-Module sqlserver

# set XML configuration file location
$configurationFile = "&lt;&lt;PATH TO FILE&gt;&gt;\AttachDatabasesConfig.xml"

# set server to attach database
$server = "&lt;&lt;TARGET SERVER&gt;&gt;"

# load configuration XML file
[xml]$databases = Get-Content $configurationFile

# loop through databases in XML configuration file
foreach($database in $databases.SQL.Database){

# retrieve database name, data file names, and log file names
$databaseName = $database.name
$dataFilenames = $database.data
$logFilenames = $database.log

# set initial SQL command to attach database
$attachSql = "CREATE DATABASE [$databaseName] ON "

# loop through database files to generate SQL statement
foreach($dataFilename in $dataFilenames){
$attachSql = $attachSql + "(FILENAME = '$dataFilename'),`n"
}

# loop through database log files to generate SQL statement
$Count = $logfilenames.Count
$i = 1
foreach($logFilename in $logFilenames){
if($i -lt $Count){
$attachSql = $attachSql + "(FILENAME = '$logFilename'),`n"
}
else{
$attachSql = $attachSql + "(FILENAME = '$logFilename')"
}
$i++
}

# Add FOR ATTACH to SQL statement
$attachSql = $attachSql + " FOR ATTACH"

# attach SQL Server database
try{
Write-Host "Attaching database $databaseName..." -NoNewLine
Invoke-Sqlcmd $attachSql -Database master -QueryTimeout 3600 -ServerInstance $server -ErrorAction Stop
Write-Host "Done!"
}catch{
throw $_.Exception.Message
}
}

# import sqlserver powershell module

Import-Module sqlserver

# set XML configuration file location

$configurationFile = "<<PATH TO FILE>>\AttachDatabasesConfig.xml"

# set server to attach database

$server = "<<TARGET SERVER>>"

# load configuration XML file

[xml]$databases = Get-Content $configurationFile

# loop through databases in XML configuration file

foreach($database in $databases.SQL.Database){

# retrieve database name, data file names, and log file names

$databaseName = $database.name

$dataFilenames = $database.data

$logFilenames = $database.log

# set initial SQL command to attach database

$attachSql = "CREATE DATABASE [$databaseName] ON "

# loop through database files to generate SQL statement

foreach($dataFilename in $dataFilenames){

$attachSql = $attachSql + "(FILENAME = '$dataFilename'),`n"

}

# loop through database log files to generate SQL statement

$Count = $logfilenames.Count

$i = 1

foreach($logFilename in $logFilenames){

if($i -lt $Count){

$attachSql = $attachSql + "(FILENAME = '$logFilename'),`n"

}

else{

$attachSql = $attachSql + "(FILENAME = '$logFilename')"

}

$i++

}

# Add FOR ATTACH to SQL statement

$attachSql = $attachSql + " FOR ATTACH"

# attach SQL Server database

try{

Write-Host "Attaching database $databaseName..." -NoNewLine

Invoke-Sqlcmd $attachSql -Database master -QueryTimeout 3600 -ServerInstance $server -ErrorAction Stop

Write-Host "Done!"

}catch{

throw $_.Exception.Message

}

How to Detach Databases

This is the same script as before except there is no need to loop through the database files. We only need to retrieve the database name and run sp_detach_db: –

Test drive FlashBlade

# import sqlserver powershell module
Import-Module sqlserver

# set XML configuration file location
$ConfigurationFile = "&lt;&lt;PATH TO FILE&gt;&gt;\AttachDatabasesConfig.xml"

# set server to attach database
$server = "&lt;&lt;TARGET SERVER&gt;&gt;"

# load configuration XML file
[xml]$databases = Get-Content $configurationFile

# loop through databases in XML configuration file
foreach($database in $databases.SQL.Database){

# retrieve database name
$databaseName = $database.name

# set initial SQL command to detach database
$attachSql = "EXEC sp_detach_db [$databaseName]"

# detach SQL Server database
try{
Write-Host "Detaching database $databaseName..." -NoNewLine
Invoke-Sqlcmd $attachSql -Database master -QueryTimeout 3600 -ServerInstance $server -ErrorAction Stop
Write-Host "Done!"
}catch{
throw $_.Exception.Message
}
}

# import sqlserver powershell module

Import-Module sqlserver

# set XML configuration file location

$ConfigurationFile = "<<PATH TO FILE>>\AttachDatabasesConfig.xml"

# set server to attach database

$server = "<<TARGET SERVER>>"

# load configuration XML file

[xml]$databases = Get-Content $configurationFile

# loop through databases in XML configuration file

foreach($database in $databases.SQL.Database){

# retrieve database name

$databaseName = $database.name

# set initial SQL command to detach database

$attachSql = "EXEC sp_detach_db [$databaseName]"

# detach SQL Server database

try{

Write-Host "Detaching database $databaseName..." -NoNewLine

Invoke-Sqlcmd $attachSql -Database master -QueryTimeout 3600 -ServerInstance $server -ErrorAction Stop

Write-Host "Done!"

}catch{

throw $_.Exception.Message

}

Check out some of these blogs for more SQL-related content:

How to Attach/Detach SQL Server Databases with PowerShell

How to Detach Server Databases

How to attach Server Databases

How to Detach Databases

Streamlining Azure VMware Solution: Automating Pure Cloud Block Store Expansion

Taming the Storage Sprawl: Simplify Your Life with Fan-in Replication for Snapshot Consolidation

Harnessing Static and Dynamic Code Scanning in DevSecOps

Why IOPS Don’t Matter

Streamlining Azure VMware Solution: Automating Pure Cloud Block Store Expansion

Taming the Storage Sprawl: Simplify Your Life with Fan-in Replication for Snapshot Consolidation

Harnessing Static and Dynamic Code Scanning in DevSecOps

Why IOPS Don’t Matter

Notes from a Hackathon: How to Cut Down Web Requests by 70%

How to Attach/Detach SQL Server Databases with PowerShell

How to Detach Server Databases

How to attach Server Databases

Related Stories

Top Stories