Everpure
Everpure

How to Attach/Detach SQL Server Databases with PowerShell

Here are some scripts I developed for the SQL Server 2012 Reference Architecture to automate how you attach and detach SQL Server databases.

SQL Server
2–3 minutes
Share
image_pdfimage_print

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

<?xml version="1.0" encoding="utf-8"?>
<SQL>
<Database>
<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>
<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'

}

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 = "<<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 = "<<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: 

Top Stories