enter code here
Param(
  [parameter(Mandatory=$True)]
        [string] $SqlServer,

       [parameter(Mandatory=$False)]
        [int] $SqlServerPort = 1433,



  [Parameter(Mandatory=$True,Position=1)]
  [string]$Sourcedatabase,


[Parameter(Mandatory=$True,Position=2)]
  [string]$backupType,

  [Parameter(Mandatory=$True,Position=3)]
  [string]$SqlUsername,


[Parameter(Mandatory=$True,Position=4)]
  [string]$SqlPass
)

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

#input paramters
$Logfile = "D:PwerShell ScriptlogBackupRestoreLog.log"
$Today=Get-Date -format s
$Today = $Today.ToString()

$Messagetext = 'Input source database is ' + $Sourcedatabase
$message = $today + ' ' + $Messagetext
   add-content $logfile -value  $message

$backupservername = "cp01gpsqlbackup"
$destinationservername = "cp01gpsqlbackup"
#Set variables
$backupserver = new-object  ('Microsoft.SqlServer.Management.Smo.Server') $backupservername

##$backupserver.ConnectionContext.StatementTimeout = 0

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

##$destinationserver.ConnectionContext.StatementTimeout = 0




##$Sourcedatabase = Read-Host -Prompt 'Enter source database Name :'

$Destinationdatabase = "LVSSPSandBox"



##$backupType= Read-Host -Prompt 'Enter Backup Type (Take new or use previous day):'

$bkdir = "D:Backup"


$UserName = "chempointop-myadav"
$File = "\cp01gpsqlbackupPwerShell ScriptPassword.txt"
$MyCredential=New-Object -TypeName System.Management.Automation.PSCredential `
-ArgumentList $UserName, (Get-Content $File | ConvertTo-SecureString)



#SqlUsername = $SqlCredential.UserName
#$SqlPass = $SqlCredential.GetNetworkCredential().Password


Function Restore
{
   Param([parameter(Mandatory=$true)] $paramdatabases,$parampath,$paramdestinationserver)


    $dbRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
                $dbRestore.Server=tcp:$using:SqlServer
                $dbRestore.$using:SqlServerPort
    $dbRestore.Database=$paramdatabases
    $dbRestore.Action = "Database"
                $dbRestore.UserID=$using:SqlUsername;
                $dbRestore.Password=$using:SqlPass;
    $dbRestore.NoRecovery = $false;
    $dbRestore.ReplaceDatabase = $true;
    $dbRestorePercentCompleteNotification = 5;
    $dbRestore.Devices.AddDevice($parampath, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

  #execute the restore!
    $dbRestore.SqlRestore($paramdestinationserver)
  }



        IF ($backupType -eq 'Take new')
        {
                IF ($backupservername -eq $destinationservername -and $Sourcedatabase -eq $Destinationdatabase)
                    {
                    Write-Host 'Both Source and destiantion servers and databases are same. Cannot proceed further'

                       }
                  Else
                      {

                      $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
                      $dbbk.Action = 'Database'
                      $dbbk.BackupSetDescription = "FULL - " + $Sourcedatabase
                      $dbbk.BackupSetName = $Sourcedatabase + " FULL Backup"
                      $dbbk.Database = $Sourcedatabase
                      $dbbk.MediaDescription = "Disk"
                      $dbbk.Devices.AddDevice($bkdir + "" + $Sourcedatabase + "_Current" + ".bak", 'File')
                      $dbbk.SqlBackup($backupserver)
                      $path = $bkdir + "" + $Sourcedatabase + "_Current" + ".bak"



                      Restore -paramdatabases $Destinationdatabase -parampath $path -paramdestinationserver $destinationserver

                     #Remove the current backup file
                      Remove-Item $path
                      }
        }

        ELSEIF($backupType -eq 'use previous day')
        {

                    #Need to add latest of file
                    #settings for the restore
                     $SQLQuery= $("select top 1 max(backup_finish_date),bs.database_name,[bmf].[physical_device_name] from msdb..backupset bs INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]  where database_name='"+ $Sourcedatabase +"' and bs.type='D' and [bmf].[physical_device_name] not like '%_current%'  group by bs.database_name,[bmf].[physical_device_name] order by max(backup_finish_date) desc")
                        $Connection = New-Object System.Data.SQLClient.SQLConnection
                        $Connection.ConnectionString = "server='$backupservername';database=msdb;trusted_connection=true;"
                        $Connection.Open()
                        $Command = New-Object System.Data.SQLClient.SQLCommand
                        $Command.Connection = $Connection
                        $Command.CommandText = $SQLQuery
                        $Reader = $Command.ExecuteReader()
                        while ($Reader.Read()) {
                             $path=$Reader.GetValue(2)
                        }
                        $Connection.Close()

                        Restore -paramdatabases $Destinationdatabase -parampath $path -paramdestinationserver $destinationserver

         }
enter code here