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

Choose Your Path with Simplified Release Lines

National Coding Week: Upskill Your Coding Knowledge with Pure Storage

Bringing Data Analytics into the Hardware Design Story

Auto-on SafeModeTM: Default Ransomware Protection

VM Performance on Flash Storage, Part 1: Alignment

VM Performance on Flash Storage, Part 2: Thin vs. Thick Provisioning

Pure Storage’s Take on Server Flash Caching

Why Flash Enables Virtualization to the Fullest

Benchmarking Database Storage

How to Attach/Detach SQL Server Databases with PowerShell

How to Detach Server Databases

How to attach Server Databases

Related Stories

Top Stories