Need Help with the script

Welcome Forums General PowerShell Q&A Need Help with the script

This topic contains 2 replies, has 2 voices, and was last updated by

2 years, 6 months ago.

  • Author
  • #55285

    Points: 0
    Rank: Member

    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.

  • #55286

    Points: 0
    Rank: Member

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

  • #55292

    Points: 639
    Helping Hand
    Rank: Major Contributor

    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:

    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.

The topic ‘Need Help with the script’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort