CODEWITHSUNDEEP
sql-serverpowershell
user3120927
user3120927

Reputation: 17

Powershell to restore database(SQL Server)

I am planning automate the restore process for SQL Server using Powershell. I don't get any error when I execute the below powershell script. Any help will be appreciated. I am not able to execute the below powershell script successfully. 

[string]  $SourceServer= ".\LOCAL"
[string]  $DestinationServer= ".\LOCAL2"
[string]  $SourceDatabase = "msdb"
[string]  $DestinationDatabase = "master"
[string]  $RestoreDatabase="test1"
[string]  $DataFolder='C:\Program Files\Microsoft SQL Server\MSSQL11.LOCAL2\MSSQL\DATA'
[string]  $LogFolder='C:\Program Files\Microsoft SQL Server\MSSQL11.LOCAL2\MSSQL\DATA'

[string]  $FullBackupSourceQuery= $("SELECT REPLACE (c.physical_device_name,'C:\SQL Server\Backup\','\\servername\Backup\')
FROM msdb.dbo.backupset a
INNER JOIN (SELECT database_name , backupdate = MAX(backup_finish_date)
FROM msdb.dbo.backupset 
WHERE type = 'D' AND 
backup_finish_date >= DATEADD(MONTH , -1 , GETDATE())
AND  database_name =$RestoreDatabase
GROUP BY database_name)b
ON a.database_name=b.database_name
AND a.backup_finish_date=b.backupdate
INNER JOIN msdb.dbo.backupmediafamily c 
ON c.media_set_id = a.media_set_id")

[string]  $DiffBackupSourceQuery= $("SELECT REPLACE (c.physical_device_name,'C:\SQL Server\Backup\','\\servername\Backup\')
FROM msdb.dbo.backupset a
INNER JOIN (SELECT database_name , backupdate = MAX(backup_finish_date)
FROM msdb.dbo.backupset 
WHERE type = 'I' AND 
backup_finish_date >= DATEADD(MONTH , -1 , GETDATE())
AND  database_name =$RestoreDatabase
GROUP BY database_name)b
ON a.database_name=b.database_name
AND a.backup_finish_date=b.backupdate
INNER JOIN msdb.dbo.backupmediafamily c 
ON c.media_set_id = a.media_set_id")

[string] $DestinationQuery=$("
EXEC master..RestoreDatabase
       @BackupFile = '$value',
       @NewDatabaseName = '$RestoreDatabase',
       @AdditionalOptions='STATS=5, REPLACE, NORECOVERY',
       @DataFolder = '$DataFolder',
       @LogFolder = '$LogFolder',
       @ExecuteRestoreImmediately = 'Y'

EXEC master..RestoreDatabase
       @BackupFile = '$value1',
       @NewDatabaseName = '$RestoreDatabase',
       @AdditionalOptions='STATS=5, REPLACE, RECOVERY',
       @DataFolder = '$DataFolder',
       @LogFolder = '$LogFolder',
       @ExecuteRestoreImmediately = 'Y'
")

function GenericSqlQuery ($SourceServer, $SourceDatabase, $SourceQuery, $DestinationServer, $DestinationDatabase, $DestnationQuery) 
{
  {
    $SourceConnection = New-Object System.Data.SQLClient.SQLConnection
    $SourceConnection.ConnectionString = "server='$SourceServer';database='$SourceDatabase';trusted_connection=true;"
    $SourceConnection.Open()
    $SourceCommand = New-Object System.Data.SQLClient.SQLCommand
    $SourceCommand.Connection = $Connection
    $SourceCommand.CommandText = $FullBackupSourceQuery
    $SourceReader = $Command.ExecuteReader()
    while ($SourceReader.Read())  {
         $value=$SourceReader.GetValue($1)
                                  }
    $SourceCommand.CommandText = $DiffBackupSourceQuery

    $SourceReader = $Command.ExecuteReader()
    while ($SourceReader.Read()) {
         $value1=$SourceReader.GetValue($1)
                                 }
   }
      $SourceConnection.Close()
  {
    $DestinationConnection = New-Object System.Data.SQLClient.SQLConnection
    $DestinationConnection.ConnectionString = "server='$DestinationServer';database='$DestinationDatabase';trusted_connection=true;"
    $DestinationConnection.Open()
    $DestinationCommand = New-Object System.Data.SQLClient.SQLCommand
    $DestinationCommand.Connection = $Connection
    $DestinationCommand.CommandText = $DestinationQuery
    $DestinationReader = $Command.ExecuteReader()
  }
    $DestinationConnection.Close()
}

Upvotes: 0

Views: 799

Answers (1)

user847990
user847990

Reputation:

Right off your function GenericSqlQuery contains a variable called $1 which I do not see set to anything. In perl that was a special variable, but in PowerShell it has no meaning until you set it to something.

As noted though a more efficient method, that includes logging and validations, is to utilize dbatools module for the restores. [Disclosure: I am a contributor to this project.]

#if not installed
Install-Module dbatools

# import it
Import-Module dbatools

# Restore it
$sourceServer = '.\LOCAL'
$DestServer = '.\LOCAL2'
$RestoreDb = 'test1'
$DataFolder='C:\Program Files\Microsoft SQL Server\MSSQL11.LOCAL2\MSSQL\DATA'
$LogFolder='C:\Program Files\Microsoft SQL Server\MSSQL11.LOCAL2\MSSQL\DATA'

# IF you need to get backup history
Get-DbaRestoreHistory -SqlServer $sourceServer -Databases $RestoreDb | 
    Restore-DbaDatabase -SqlServer $DestServer -DestinationDataDirectory $DataFolder -DestinationLogDirectory $LogFolder

# IF you just want to base it on backup folder, will SCAN complete folder
$dbBackupPath = "\\servername\Backup\$RestoreDb"
Restore-DbaDatabase -SqlServer $DestServer -Path $dbBackupPath -DestinationDataDirectory $DataFolder -DestinationLogDirectory $LogFolder

Upvotes: 1

Related Questions