Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.

attach_a

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.

attach_b

I also had a bunch of mdf/ldf files in a folder. As well as some folders for Filestream/Filetable information.

attach_c

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:

attach_f

In SSMS, I had my databases.

attach_d

I even had my Filestream stuff in place. SQL Server handled that for me.

attach_e

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.


Filed under: Blog Tagged: administration, Backup/Recovery, powershell, sql server, syndicated

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...