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: –
How to Detach Server Databases
Detach-SQLDatabases.ps1; this is the same script as in the Attach-SQLDatabases.ps1 except for the $attachSQLCMD statement using sp_detach.
How to attach 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: –
How to Detach 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: –
- A Question for SQL DBAs: To Back Up or To Recover?
- Restore SQL Server Data Faster with Pure’s SSMS Extension
- Why to Upgrade from SQL Server 2008
- Move on Up Stack With Kubernetes and SQL Server
- Pure Storage Snapshots with SQL Server Availability Groups
- SQL Server on Pure: Initializing Replication Subscribers With Snapshots… In Seconds!
