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.
$restoredName = $dbname
if($REQ -ne $null -and $REQ.Length -gt 0)
'Not a Valid Database Name'
$networkshare = '\\Domain\Test\'
$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
$restoredNameMDFFileName = 'C:\Test\SQLData\' +$restoredName + '.mdf' ;$restoredNameMDFFileName
$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 '\'.
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: 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.