none
Attach SQL DB via powershell

    Question

  • So I have this script:

    "# Attach DB
    Write-Host "Attaching Trout DB"
    $datastr = $NewMDFFileLocation + "\" + $MDFFileName
    $logstr = $NewLDFFileLocation + "\" + $LDFFileName
    $d = $srv.DetachedDatabaseInfo($datastr)
    foreach ($r in $d.Rows)
    {
       Write-Host "=========================="
       Foreach ($c in $d.Columns)
       {
          Write-Host $c.ColumnName "=" $r[$c].ToString()
       }
    }

    Write-Host $srv.IsDetachedPrimaryFile($datastr)
    $sc = new-object System.Collections.Specialized.StringCollection
    $sc.Add($datastr)
    $sc.Add($logstr)
    $srv.AttachDatabase($DBName, $sc, $owner, [Microsoft.SqlServer.Management.Smo.AttachOptions]::None)
    Write-Host "Done"

     

    When the DB was detached, the mdf and ldf files were in a different directory. When this script runs it correctly sets the mdf location, however, it seems ot ignore the ldf setting. When it attempt to reattach the DB it still looks in the old directory for the ldf file and ignores the setting in $logstr.

    My goal is to move mdf and ldf files to another directory via powershell script ( I have about half a dozen I need to move in an automated fashion). These are BRAND new DBs so these files are very small.

    The detach and move scripts work fine, this one is the last piece of the puzzle. Any help anyone can give would be greatly appreciated.

     

    Friday, December 30, 2011 12:35 PM

Answers

    • Proposed as answer by Peja Tao Monday, January 02, 2012 4:51 AM
    • Marked as answer by Peja Tao Monday, January 16, 2012 1:11 AM
    Friday, December 30, 2011 12:37 PM
  • The script you have is very similar or the same as on the Technet website, but I rewrote a couple of things to make things a little simpler.

    I was able to reattach a database that was detached after I moved the files to the new locations and used the locations in the $datafile and the $logfile variables.

    Here is the completed script.  It does not do the move, but I verified that as long as the Trout.mdf file was in the c:\newsqldata folder, and the Trout_log.ldf file is in the c:\newlogdata folder, then the attach would use those values and attach the database.  I used the SQL Provider that is only available in SQL 2008, so if you are using SQL 2005 then it will not work and you can get your $srv object from another way and it will still work.

    # Attach DB
     Add-PSSnapin *SQL*

     $srv = (Get-Item sqlserver:\sql\servername\desk)

     Write-Host "Attaching Trout DB"
     $MDFFileName = "Trout.mdf"
     $LDFFileName = "Trout_log.LDF"
     $DBName = "TroutDB"
     $owner = "sa"

     $datastr = "c:\newsqldata\"
     $logstr = "c:\newlogdata\"
     $datafile = "$datastr$MDFFileName"
     $logfile =  "$logstr$LDFFileName"
     $d = $srv.DetachedDatabaseInfo($datafile)
     foreach ($r in $d.Rows)
     {
        Write-Host "=========================="
        Foreach ($c in $d.Columns)
        {
           Write-Host $c.ColumnName "=" $r[$c].ToString()
        }
     }
     
    Write-Host $srv.IsDetachedPrimaryFile($datafile)
     $sc = new-object System.Collections.Specialized.StringCollection
     $sc.Add($datafile)
     $sc.Add($logfile)
     $srv.AttachDatabase($DBName, $sc, $owner, "None")
     Write-Host "Done"


    Ben Miller
    Friday, December 30, 2011 10:33 PM

All replies

    • Proposed as answer by Peja Tao Monday, January 02, 2012 4:51 AM
    • Marked as answer by Peja Tao Monday, January 16, 2012 1:11 AM
    Friday, December 30, 2011 12:37 PM
  • The script you have is very similar or the same as on the Technet website, but I rewrote a couple of things to make things a little simpler.

    I was able to reattach a database that was detached after I moved the files to the new locations and used the locations in the $datafile and the $logfile variables.

    Here is the completed script.  It does not do the move, but I verified that as long as the Trout.mdf file was in the c:\newsqldata folder, and the Trout_log.ldf file is in the c:\newlogdata folder, then the attach would use those values and attach the database.  I used the SQL Provider that is only available in SQL 2008, so if you are using SQL 2005 then it will not work and you can get your $srv object from another way and it will still work.

    # Attach DB
     Add-PSSnapin *SQL*

     $srv = (Get-Item sqlserver:\sql\servername\desk)

     Write-Host "Attaching Trout DB"
     $MDFFileName = "Trout.mdf"
     $LDFFileName = "Trout_log.LDF"
     $DBName = "TroutDB"
     $owner = "sa"

     $datastr = "c:\newsqldata\"
     $logstr = "c:\newlogdata\"
     $datafile = "$datastr$MDFFileName"
     $logfile =  "$logstr$LDFFileName"
     $d = $srv.DetachedDatabaseInfo($datafile)
     foreach ($r in $d.Rows)
     {
        Write-Host "=========================="
        Foreach ($c in $d.Columns)
        {
           Write-Host $c.ColumnName "=" $r[$c].ToString()
        }
     }
     
    Write-Host $srv.IsDetachedPrimaryFile($datafile)
     $sc = new-object System.Collections.Specialized.StringCollection
     $sc.Add($datafile)
     $sc.Add($logfile)
     $srv.AttachDatabase($DBName, $sc, $owner, "None")
     Write-Host "Done"


    Ben Miller
    Friday, December 30, 2011 10:33 PM
  • Ben - would it be possible to adapt this script to pick up any detached MDF/LDF in a specific directory and attach it to a database?

    Example: scan X:\SQL for detached data files and Y:\SQL for detached log files, pair them together (assuming they have the same name) and mount them as the datafile's name.

    • Edited by imbrian21 Wednesday, November 13, 2013 9:02 PM
    Wednesday, November 13, 2013 8:58 PM