Friday, October 14, 2011

SQL and Powershell: Attach all database mdf files in a directory

Hey all,
I didn’t find this elsewhere on the web and I needed it. Since I actually had to do work, i figure I should give it out and spare somebody else that pain.
As part of a failover plan, I am synchronizing directories of sql files. In a failover, I am going to need to bring up a group of databases in a specific directory. The script below will take in a directory name and a SQL instance. It will get all the .mdf files in the directory and compare them against the database files attached to the SQL instance. Any files that are not attached already are attached, extrapolating the filename from the .mdf file. The MAJOR ASSUMPTION here is that you want to name your Database the same name as the .mdf file (without the .mdf). Several of my dbs have multiple files so I read the .mdf to get all the files it knows about. Another major ASSUMPTION is that you are replicating to/attaching from the same file paths on your new server.
I use my out-log function. if you don’t want to use it, replace out-log with write-host in the below script.

function AttachMDFsInDir {
      param (
            $dir = $( throw "Must specify -dir" ),
            $instance  = $( throw "Must specify -instance" ),
            $verbosity = 1 # for the out-log function.
      out-log "Starting AttachMDFsInDir with dir: $dir and inst: $instance"
      $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance)
      out-log "Connected to $($srv.Name)"
      #get a list of all attached database file names
      $colAttachedMDFs = @()
      foreach ($db in $srv.Databases ) {
            foreach ($fg in $db.Filegroups) {
                  foreach ($file in $fg.Files) {
                        out-log "Adding to `$colattachedMDFs: $($file.Filename)" 2
                        $colAttachedMDFs += $file.Filename
      out-log "Found $($colAttachedMDFs.count) mdfs attached in $instance"         
      #foreach mdf in $groupSQLDir
      $colMDFsToAttach = @()
      foreach ($mdf in dir "$dir\*.mdf") {
            if (-not ($colAttachedMDFs -contains $mdf)) {
                  out-log "Adding to `$colMDFsToAttach: $($mdf.Fullname)" 2
                  $colMDFsToAttach += $mdf.FullName
      Remove-Variable mdf
      Remove-Variable file
      out-log "Found $($colMDFsToAttach.count) mdf(s) in the directory that are unattached"           

      foreach ($mdf in $colMDFsToAttach) {
            #determine name to restore as from mdf file name.
            $DBNameFromFile = ([System.IO.FileInfo]$mdf).Basename
            out-log "Named the db $DBNameFromFile based on the mdf: $mdf"

            #get list of files
            $colDBFiles = $srv.EnumDetachedDatabaseFiles($mdf)
            out-log "$mdf specifies $($coldbfiles.count) files to attach."
            out-log $colDBFiles 2
            #add to collection
            $files = new-object system.collections.specialized.stringcollection
            foreach ($file in $colDBFiles) {
            out-log "Ready to attach db: $DBNameFromFile w/ $($files.count) files."
            #attach database

As a corollary to the above, i needed to check which of my dbs on the running servers did not have the same name as their .mdf. you can use this loop.

$instances = @("SERV\Inst1","Serv\INST2") )
foreach ($instance in $instances) {
      $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance)
      foreach ($db in $srv.Databases ) {
            foreach ($fg in $db.Filegroups) {
                  foreach ($file in $fg.Files) {
                        $Fileinf = [System.IO.FileInfo]$file.filename
                        if ($Fileinf.extension -eq ".mdf" ) {
                              if ($Fileinf.BaseName -eq $ {
                                    #"File: $($Fileinf.Basename) matches $($"
                              } else {
                                    "Filename: $($Fileinf.Basename) DOES NOT MATCH DB Name: $($"

1 comment: