Attach/Detach SQL Server Databases with PowerShell

While working on the early testing for the SQL Server 2012 Reference Architecture I had to create some scripts to automate the attaching and detaching of SQL Server databases so I could continue to scale up my testing efforts. 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 into dev/test, reporting clusters or other scenarios.

Attach-SQLDatabases.ps1; the first part of this script loads an XML configuration file which you can expand to however many databases (MDF/LDF) and names that you desire. The XML configuration file is detailed a bit further down.

#
# 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

    #
    # Attach 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
CREATE DATABASE [$DBName] ON (FILENAME = '$mdfFilename.mdf'),(FILENAME = '$ldfFilename.ldf') for ATTACH
GO
"@ 
    Invoke-Sqlcmd $attachSQLCMD -QueryTimeout 3600 -ServerInstance 'MSFT-DEMOBOX-01\PURE1'

}

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'

}

AttachDatabasesConfig.xml; this XML can be expanded as necessary and the scripts above will just read in the individual databases nodes.

<?xml version="1.0" encoding="utf-8"?>
<SQL>
  <Databases>
    <MDF>G:\Database1_data</MDF>
    <LDF>G:\Database1_log</LDF>
    <DB_Name>AdventureWorks_DB1</DB_Name>
  </Databases>
  <Databases>
    <MDF>H:\Database2_data</MDF>
    <LDF>H:\Database2_log</LDF>
    <DB_Name>AdventureWorks_DB2</DB_Name>
  </Databases>
  <Databases>
    <MDF>I:\Database3_data</MDF>
    <LDF>I:\Database3_log</LDF>
    <DB_Name>AdventureWorks_DB3</DB_Name>
  </Databases>
  <Databases>
    <MDF>J:\Database4_data</MDF>
    <LDF>J:\Database4_log</LDF>
    <DB_Name>AdventureWorks_DB4</DB_Name>
  </Databases>
  <Databases>
    <MDF>K:\Database5_data</MDF>
    <LDF>K:\Database5_log</LDF>
    <DB_Name>AdventureWorks_DB5</DB_Name>
  </Databases>
</SQL>
  • Brian

    This is great! Thanks a ton for all the content on this blog. Loving powershell.

    • Glad you find the information helpful. Keep on loving and living PowerShell!

      Cheers,
      Barkz

  • Brian

    One thing to note. In the comment block for the detach database script, it says ‘attach’ database. Not a big deal. Thought I would point it out.

    • The devil’s in the details 😉 Thanks for pointing out and I’ve updated the article.

      Cheers,
      Barkz

  • KP

    Would modifications need to be made in both Attach-SQLDatabases and the XML file for dbs that include multiple MDFs and potentially NDFs? If you listed all the and included within the XML, would you just need to include an $ndfFilename in the PS1 – and would the attach properly include all MDFs and NDFs specified?

    • Hi KP –

      Yes if you added the necessary details about the M/L/NDF(s) and then modified “CREATE DATABASE [$DBName] ON (FILENAME = ‘$mdfFilename.mdf’),(FILENAME = ‘$ldfFilename.ldf’) for ATTACH” to add those extra files this will work.

      Thanks,
      Barkz