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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
# # 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
# # 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<?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> |
