Attaching All Databases with PowerShell – The Overview
TL;DR Script is here: Git Hub Powershell Scripts. It’s the attachdbs.ps1 and will attach all databases in a folder to a SQL Server instance, if they don’t exist.
I wrote a PowerShell script recently to actually accomplish a task I that I needed. What’s more, this was the first time I thought that Powershell might prove more useful than other methods. This series looks at my script, and this part examines the first part that I wrote.
After my problems with Windows 8.1 and my reinstallation of SQL Server, I had a problem. I had no databases.
I had the files. I had backup files. However the instance didn’t have any databases registered. I started down this path.
However that seemed inefficient. I actually had a pattern of things that I knew needed to be done, I had a bunch of repeatable work, this sounded like it should be a PowerShell type task. I could have done it in T-SQL, or grabbed a script from SQLServerCentral, but it made more sense to load databases with PowerShell.
Of course I started Googling, but didn’t see any posts that shower someone with mdf/ldf files and needing to attach them to an instance without knowing what you had. What I had was an instance, with no backup/restore/detach history.
I also had a bunch of mdf/ldf files in a folder. As well as some folders for Filestream/Filetable information.
This post will give an overview of what I needed to do and I’ll post more details about how I built the script in pieces. The overview of the process is:
- Get all MDF Files in a folder
- Connect to a SQL Server instance and loop through all databases
- If a file name (less the .mdf) does not exist as a database, track this.
- Get the log file associated with an mdf
- Attach the mdf and ldf files to the SQL Server.
That’s what I needed to do and development went in those stages. Certainly there were issues, but I got it working as of this post. When I ran my script, I saw these results:
In SSMS, I had my databases.
I even had my Filestream stuff in place. SQL Server handled that for me.
I’ll include other posts that talk about the details of how I build this, which took about 3 hours one day, and an hour the next.
Here are a few posts where I picked up bits and pieces of what I needed to do.