SQL

clear

#---------------------------------------------------
$server="mysqlserver"
$endpoint_port="5022"
$instance="myNamedSQLinstance"
$emailFrom = "provisioning@mydomain.com"
$emailTo = "mathieu.chateau@lotp.fr"
$smtpServer = "mysmtpserver"
$sendmail=$true
$foldersource='\\myfileserver\backupFromDev$'
$folderarchived='\\myfileserver\backupFromDev$\done'
$excludeList=@()
$excludeList+='master'
$excludeList+='tempdb'
$excludeList+='model'
#---------------------------------------------------
$foldersource="c:\"
$folderarchived="F:\"

$majorError=$true
$global:log=@()
try
{
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
    $source = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$server\$instance"
    $majorError=$false
}
catch
{
       AddLog "major error, one side not reachable $_"
       "major error, one side not reachable $_"
       $majorError=$true
}

function AddLog($text)
{
       $global:log+=$text
       Write-Host $text
}

if ($majorError -eq $false)
{
       $databases = $source.Databases
       foreach ($file in (Get-childitem $foldersource -Filter "*.bak"))
       {
               $dbname=$file.Name -replace (".bak","")
               if ($excludeList -notcontains $dbname)
            {
                #not on the blacklist
                if (($databases | ?{$_.Name -ieq $dbname}) -eq $null)
                {
                    #database does not exist
                    try
                    {
                        #Restore
                        $targetDBFilePath = $source.MasterDBPath + "\" + $dbName + ".mdf"
                        $targetLogFilePath = $source.MasterDBLogPath + "\" + $dbName + ".ldf"
                        AddLog "   going to restore $dbname to:"
                        AddLog "   $targetDBFilePath and $targetLogFilePath"
                        $restore = new-object ('Microsoft.SqlServer.Management.Smo.Restore')
                        $restore.Action = 'Database'
                        $restore.Database = $dbname
                        ##$backupDataFile=$dbbk.Devices[0].Name
                        #$restore.Devices.AddDevice($backupDataFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                        $backupfile=New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem")($file.FullName, "File")
                        $restore.Devices.Add($backupfile)
                        #$restoredetails=$restore.ReadBackupHeader($source)
                        $relocateDataFile = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile')
                        $relocateLogFile  = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile')
                        $dbFileList = $restore.ReadFileList($source)
                        $relocateDataFile.LogicalFileName = $dbFileList.Select("Type = 'D'")[0].LogicalName
                        $relocateDataFile.PhysicalFileName = $targetDBFilePath
                        $relocateLogFile.LogicalFileName = $dbFileList.Select("Type = 'L'")[0].LogicalName
                        $relocateLogFile.PhysicalFileName = $targetLogFilePath
                        $restore.RelocateFiles.Add($relocateDataFile)
                        $restore.RelocateFiles.Add($relocateLogFile)
                        $restore.ReplaceDatabase = $False
                        $restore.NoRecovery = $False
                        $restore.SqlRestore($source)
                        AddLog "     Restore done"
                        Move-Item $file.FullName $folderarchived
                    }
                    catch
                    {
                        AddLog "    error during restore: $_"
                        $majorError=$true
                        break
                    }
                    
                }
                else
                {
                    #blacklisted name
                    AddLog "database $dbname / $($files.Names) already exist"
                }
            }
            else
            {
                AddLog "Forbidden databasename: $dbname"
            }
       
       }
}

if($sendmail)
{
       $temp=""
       foreach ($line in $log)
       {
              $temp+=$line+"`r`n"
       }
       $smtp = new-object Net.Mail.SmtpClient($smtpServer)
       $smtp.Send($emailFrom, $emailTo,"SQL mirror automator",$temp)
}


$log