Attach/Detach SQL Server Databases with PowerShell

@8arkz — June 20/17 — I need to re-write this to use the new PowerShell SDK.

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>
8 comments
  1. 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.

  2. 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

    • I created some workaround which helped make the work faster:

      create table model..Databases (DB_Name varchar(50), MDF varchar(200), LDF varchar(200))
      Insert into model..Databases select distinct s.name, s.filename, p.physical_name
      from master..sysdatabases s
      left join master.sys.master_files p
      on s.dbid = p.database_id
      left join master.sys.sysaltfiles a
      on p.database_id = a.dbid
      where database_id> 4 and is_sparse = 0 and p.physical_name LIKE ‘%.ldf%’

      declare @cmd nvarchar(450);

      select @cmd =

      ‘bcp “use model; Select * from Databases FOR XML AUTO, root(”SQL”), elements” ‘+ ‘queryout “E:AttachDatabasesConfig.xml” -S ServerNameInstanceName -T -w -r -t’;

      exec xp_cmdshell @cmd;
      go

      Drop table model..Databases

Add Comment

Required fields are marked *. Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.