@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>
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
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
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
Hello – Is there anyway to automate the extraction of this XML?
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