The ability to easily attach/detach SQL databases when testing is incredibly valuable. Whilst working on a procedure to restore corrupted data in a database I had to create some scripts to automate attaching and detaching of SQL Server databases.
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.
First thing to do is create a AttachDatabasesConfig.xml file to list the databases and their corresponding data/log files to be attached. Here is an example: –
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?xml version="1.0" encoding="utf-8"?> <SQL> <Database> <name>TESTDATABASE1</name> <data>F:\data\TESTDATABASE1.MDF</data> <data>F:\data\TESTDATABASE1_2.NDF</data> <data>F:\data\TESTDATABASE1_3.NDF</data> <data>F:\data\TESTDATABASE1_4.NDF</data> <log>F:\log\TESTDATABASE1_log.LDF</log> </Database> <Database> <name>TESTDATABASE2</name> <data>F:\data\TESTDATABASE2.MDF</data> <data>F:\data\TESTDATABASE2.NDF</data> <log>F:\log\TESTDATABASE2_log.LDF</log> </Database> </SQL> |
How to Detach SQL Server Databases
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' } |
How to attach SQL Server Databases
Once the XML configuration file is created, the following script will retrieve the information in the file and attach the databases to a target instance: –
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
# import sqlserver powershell module Import-Module sqlserver # set XML configuration file location $configurationFile = "<<PATH TO FILE>>\AttachDatabasesConfig.xml" # set server to attach database $server = "<<TARGET SERVER>>" # load configuration XML file [xml]$databases = Get-Content $configurationFile # loop through databases in XML configuration file foreach($database in $databases.SQL.Database){ # retrieve database name, data file names, and log file names $databaseName = $database.name $dataFilenames = $database.data $logFilenames = $database.log # set initial SQL command to attach database $attachSql = "CREATE DATABASE [$databaseName] ON " # loop through database files to generate SQL statement foreach($dataFilename in $dataFilenames){ $attachSql = $attachSql + "(FILENAME = '$dataFilename'),`n" } # loop through database log files to generate SQL statement $Count = $logfilenames.Count $i = 1 foreach($logFilename in $logFilenames){ if($i -lt $Count){ $attachSql = $attachSql + "(FILENAME = '$logFilename'),`n" } else{ $attachSql = $attachSql + "(FILENAME = '$logFilename')" } $i++ } # Add FOR ATTACH to SQL statement $attachSql = $attachSql + " FOR ATTACH" # attach SQL Server database try{ Write-Host "Attaching database $databaseName..." -NoNewLine Invoke-Sqlcmd $attachSql -Database master -QueryTimeout 3600 -ServerInstance $server -ErrorAction Stop Write-Host "Done!" }catch{ throw $_.Exception.Message } } |
How to Detach SQL Server Databases
This is the same script as before except there is no need to loop through the database files. We only need to retrieve the database name and run sp_detach_db: –
|
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 29 30 |
# import sqlserver powershell module Import-Module sqlserver # set XML configuration file location $ConfigurationFile = "<<PATH TO FILE>>\AttachDatabasesConfig.xml" # set server to attach database $server = "<<TARGET SERVER>>" # load configuration XML file [xml]$databases = Get-Content $configurationFile # loop through databases in XML configuration file foreach($database in $databases.SQL.Database){ # retrieve database name $databaseName = $database.name # set initial SQL command to detach database $attachSql = "EXEC sp_detach_db [$databaseName]" # detach SQL Server database try{ Write-Host "Detaching database $databaseName..." -NoNewLine Invoke-Sqlcmd $attachSql -Database master -QueryTimeout 3600 -ServerInstance $server -ErrorAction Stop Write-Host "Done!" }catch{ throw $_.Exception.Message } } |
