Need Help with the script

I am trying to write the below mentioned query for doing a database restore dynamically.

I need to take a database backup and restore a new database on the same server with a new name

I am running into the problems in the last statement.

[Parameter(Mandatory = $true)] [String] $dbname,
[Parameter(Mandatory = $true)] [String] $REQ

Import-Module SQLPS -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null

$restoredName = $dbname

if($REQ -ne $null -and $REQ.Length -gt 0)

$restoredName = $REQ + ‘_’ + $dbName

Else {

‘Not a Valid Database Name’


$networkshare = '\Domain\Test'
$dbfilebackup = $networkshare + $DBName + ‘.bak’
$dblogbackup = $networkshare + $DBName + ‘.trn’

$backup_path = $dbfilebackup

$MDFLogicalName = Invoke-Sqlcmd -ServerInstance ‘TestServer’ -Database ‘master’ -Query " SELECT name FROM sys.master_files WHERE database_id = DB_ID(‘$dbname’) and type_desc =‘ROWS’;" ; $MDFLogicalName
$LDFLogicalName = Invoke-Sqlcmd -ServerInstance ‘TestServer’ -Database ‘master’ -Query “SELECT name FROM sys.master_files WHERE database_id = DB_ID(‘$dbname’) and type_desc =‘LOG’ ;” ; $LDFLogicalName

$restoredNameMDFFileName = 'C:\Test\SQLData' +$restoredName + ‘.mdf’ ;$restoredNameMDFFileName
$restoredNameLDFFileName = 'C:\Test\SQLLog' + $restoredName + ‘.ldf’ ;$restoredNameLDFFileName

$sqlprimary = Invoke-Sqlcmd -ServerInstance ‘TestServer’ -Database ‘TestDB’ -Query “SELECT ReplicaServerName FROM [dbo].[Replica_Types] WHERE [Replicas Type] = ‘Primary Replica’;” ; $sqlprimary

$sqlstringprimary = 'SQLSERVER:\SQL' + $sqlprimary.ReplicaServerName + ‘\DEFAULT’

Backup-SqlDatabase -Database $dbname -BackupFile $dbfilebackup -ServerInstance $sqlprimary.ReplicaServerName

$DataLogicalName = $REQ + ‘_’ +$MDFLogicalName

$LogLogicalName = $REQ + ‘_’ +$LDFLogicalName

$query = " EXECUTE [dbo].[sp_Load_filelist_Table] ‘$backup_path’ "

$Query1 = Invoke-Sqlcmd -ServerInstance ‘TestServer’ -Database ‘TestDB’ -Query $query

$query2 = 'RESTORE DATABASE ’ + $restoredName + ’ FROM DISK = ’ + $dbfilebackup + ’ WITH MOVE ’ + $DataLogicalName + ’ To ’ + $restoredNameMDFFileName + ’ , ’ + ’ MOVE ’ + $LogLogicalName + ’ TO ’ + $restoredNameLDFFileName + ’ ; ’

$query3 = Invoke-Sqlcmd -ServerInstance ‘TestServer’ -Database ‘TestDB’ -Query $query2

I am getting the below meentioned error message.

Invoke-Sqlcmd : Incorrect syntax near ''.
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a
change tracking context clause, the previous statement must be terminated with a semicolon.
The label ‘D’ has already been declared. Label names must be unique within a query batch or stored procedure.
At C:\Test.ps1:61 char:11

  • $query3 = Invoke-Sqlcmd -ServerInstance ‘TestServer’ -Database 'TestDB …
  •   + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
      + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Can you help me please.

My backup can be in network or local drive. I am more targeting towards network drive.

If you are trying to do anything in SQL that requires running commands succinctly, you should really consider making it a SQL script or even better a SQL stored procedure. The only reason I would keep it a script is if I really wanted someone that is reading the script to be able to see what was being done in SQL. procBackupAllDatabases stored procedure would be simple to understand what the code is doing without having a lengthy script. Take a look at: Simple script to backup all SQL Server databases

Rather than trying to run 4-5 SQL commands and use variables in Powershell, it can all be done in SQL and executed with a single Invoke-SQLCMD.