February 27, 2009 at 2:59 pm
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')
$dbres.devices.addDevice($f2,2)
$dbname = $dbres.readbackupheader($d)
foreach($Databasename in $dbname)
{$dbres.Database = $databasename.DatabaseName}
$dbres.action = 0
$dbres.ReplaceDatabase = 1
$dbres.RelocateFiles.Clear()
$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 microsoft.sqlserver.management.smo.relocatefile -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
$dbres.SqlRestore($d)
$d.KillDatabase($dbres.Database)
}
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.
March 5, 2009 at 8:26 pm
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.
Regars,
Terry
March 6, 2009 at 9:50 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply