Need Help with the script

Tagged: 

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 3 months, 1 week ago.

  • Author
    Posts
  • #55285
    Profile photo of Windows LiveUser175
    Windows LiveUser175
    Participant

    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.

  • #55286
    Profile photo of Windows LiveUser175
    Windows LiveUser175
    Participant

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

  • #55292
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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: https://www.mssqltips.com/sqlservertip/1070/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.

You must be logged in to reply to this topic.