Database restore error

  • I have written a powershell script to search through a folder and find any database backups and restore them for the purpose of testing database backups on a centralized server. What I have works fine but this is my first attempt at a powershell script so there may be suggestions as to what can be done better.

    One thing i would like is to be able to write to file the reason a restore did not complete properly. Currently it only writes the powershell exception that restore failed.

    $date = get-date

    write-output $date |out-file f:\logs\restore.log -append

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") |out-null

    $d = new-object ('Microsoft.SqlServer.Management.Smo.Server')' '

    $dir = " " #location of database backup files

    $ext = " "#extension of database backup files

    $f = get-childitem $dir -Recurse |Where{$_.Extension -EQ $ext}#search for all files of specified extension in specified location

    foreach($file in $f)


    $f2 = $file.fullname

    write-output "*****************************************"|out-file f:\logs\restore.log -append

    write-output "Restoring Backup file: $file"|out-file f:\logs\restore.log -append

    #Create database restore object and set restore settings

    $dbres = New-object ('Microsoft.SqlServer.Management.Smo.Restore')


    $dbname = $dbres.readbackupheader($d)

    foreach($Databasename in $dbname)

    {$dbres.Database = $databasename.DatabaseName}

    $dbres.action = 0

    $dbres.ReplaceDatabase = 1


    $dbfiles = $dbres.readfilelist($d)

    #find logical name and change restore location

    foreach ($logicalFileName in $dbfiles)


    $dbpath = " "

    if ($logicalFileName.PhysicalName -match “mdf”)


    $strRedirectFile = $dbres.Database + “.mdf”;

    $strRedirectFile = Join-Path $dbpath $strRedirectfile;


    elseif ($logicalFileName.PhysicalName -match “ldf”)


    $strRedirectFile = $dbres.Database + “.ldf”;

    $strRedirectFile = Join-Path $dbpath $strRedirectfile;


    elseif ($logicalFileName.PhysicalName -match “ndf”)


    $strRedirectFile = $dbres.Database + “.ndf”;

    $strRedirectFile = Join-Path $dbpath $strRedirectfile;


    $dbres.RelocateFiles.Add((new-object -ArgumentList $logicalFileName.LogicalName, $strRedirectFile)) | Out-Null;


    $dbres.NoRecovery = $false

    # error trapping for failed restore

    Trap { write-output $file' failed to restore ' $_.Exception.Message|out-file f:\logs\restore.log -append;

    write-output $_.Exception.GetType().FullName|out-file f:\logs\restore.log -append;

    continue; }

    # restore backup file




    also would like to be able to restore database as original name instead of hardcoded "test" that I currently have.

    I am open to any suggestions because there may be some cmdlets that I can use which i do not know about that may make it easier more robust. Thanks in advance.

    How to post to get the best help[/url]

  • Why dont you change the test for a varible, when you read in the file that must have a file name like the database its been backed up from ie acme.db read in that name

    ForEach-Object ($Database in $file) { read this into and bring this into your restore}

    $dbres.Database = $DATABASE

    I will have a look at this program a little more and edit it for you over the weekend.



  • Thanks for suggestion, I have added lines (editted code in original post,changes in bold) that get database name out of the header and apply that to the database being restored. Aslo added line at the bottom to remove restored database once it is finished. thanks

    How to post to get the best help[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply