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.

The Start

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.

What did I do? I’ve got the script on GitHub, and you can grab the latest version at: Powershell Scripts (choose the attachdbs.ps1 file)

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.

References

Here are a few posts where I picked up bits and pieces of what I needed to do.

• http://stackoverflow.com/questions/3605522/how-to-iterate-over-files-with-powershell
• http://blogs.msdn.com/b/buckwoody/archive/2009/03/19/using-powershell-and-smo-to-list-databases-and-other-stuff.aspx
• http://social.msdn.microsoft.com/Forums/sqlserver/en-US/00b886c7-e0b9-47a7-bd16-8fd1f063e4a5/attach-sql-db-via-powershell?forum=sqldatabaseengine
• http://blog.aggregatedintelligence.com/2012/02/powershell-smocopy-and-attach-database.html