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.
Param
(
[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.