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.

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

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

  • 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

  • Gabi Augusto Marcondes

    Hello – Is there anyway to automate the extraction of this XML?

    • Gabi Augusto Marcondes

      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