This is an automated archive.
The original was posted on /r/sysadmin by /u/Sufficient-West-5456 on 2024-01-19 02:43:29+00:00.
ERROR:
PS C:\Users\*user**\Projects and Scripts\PowerShellTest\Scripts\AZbkupScripts> . 'C:\Users\*user**Projects and Scripts\PowerShellTest\Scripts\AZbkupScripts\bkupsqltoaz_local2.ps1'
Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is
correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
At C:\Users\**user**\Projects and Scripts\PowerShellTest\Scripts\AZbkupScripts\bkupsqltoaz_local2.ps1:21 char:1
-
Invoke-Sqlcmd -ServerInstance $sqlServer -Database "master" -Query $b ...
-
-
CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
-
FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Invoke-Sqlcmd :
At C:\Users\***hidiingit**\Projects and Scripts\PowerShellTest\Scripts\AZbkupScripts\bkupsqltoaz_local2.ps1:21 char:1
-
Invoke-Sqlcmd -ServerInstance $sqlServer -Database "master" -Query $b ...
-
-
CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException
-
FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Error: unknown command "/Source:C:\Backup" for "azcopy"
Run 'azcopy --help' for usage.
unknown command "/Source:C:\Backup" for "azcopy"
i ENSURED instance name iisue was corected.
i ensured sas token and azure url is correct because when i run from ssms as a task- tsql, backup is copied correctly to blob.
i ensured to install azcopy 64 and 32 (we are x64 so i downloded az64 first but did nothing to solve error). i ensure to set environment path for az-copy seperately as well via single ps1 command.
main script which generated above error is here:
param (
[string]$sqlServer = "YourSqlServerInstance",
[string]$sqlDatabase = "master",
[string]$sqlUsername = "Backupadmin",
[string]$sqlPassword = "test123",
[string]$azCopyPath = "C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy\azcopy_windows_386_10.22.2",
[string]$backupPath = "C:\Backup",
[string]$containerName = "YourStorageContainerName",
[string]$destStorageAccountName = "YourDestinationStorageAccountName",
[string]$destContainerName = "YourDestinationContainerName",
[string]$sasToken = "YourSasToken" # Add this parameter for SAS token
)
SQL Server connection string
$connectionString = "Server=$sqlServer;Database=$sqlDatabase;User Id=$sqlUsername;Password=$sqlPassword;"
SQL query to execute
$backupQuery = "BACKUP DATABASE $sqlDatabase TO DISK='$backupPath\TutorialDB-$(Get-Date -Format 'yyyyMMddHHmmss').bak'"
Invoke SQL command with username and password
Invoke-Sqlcmd -ServerInstance $sqlServer -Database "master" -Query $backupQuery -Username $sqlUsername -Password $sqlPassword
AzCopy command to copy the backup to Azure Storage
$backupFileName = Get-ChildItem -Path $backupPath | Where-Object { $_.Extension -eq ".bak" } | Select-Object -ExpandProperty Name
$destinationBlobUrl = "https://$destStorageAccountName.blob.core.windows.net/$destContainerName/$backupFileName?$sasToken"
& "$azCopyPath\azcopy" /Source:$backupPath /Dest:$destinationBlobUrl /Y
Verify that the backup file exists before attempting to remove it
$backupFilePath = Join-Path -Path $backupPath -ChildPath $backupFileName
if (Test-Path -Path $backupFilePath) {
Remove-Item -Path $backupFilePath
}
tldr: anyhelp is appriciated. still new