Use Backup-SqlDatabase cmdlet with SQL Server Authentication
The backup-sqldatabase cmdlet supports the Credential parameter. If you look at the help for the cmdlet there's even an example (from help backup-sqldatabase -full):
-------------------------- EXAMPLE 4 -------------------------- C:\PS>Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -Credential (Get-Credential sa) Description ----------- This command creates a complete database backup of the database 'MyDB', using the sa SQL Server credential. This co mmand will prompt you for a password to complete SQL Server authentication.
You could connect a virtual drive before running Backup-SqlDatabase
$backupFolder = '...'$additionToName = '...'$user = 'Username'$pass = 'Password'$inst = 'Server\Instance'$db = 'master'$file = "$backupFolder${db}_db_$additionToName.bak"$root = "SQLSERVER:\SQL\$inst"$drv = 'sqldrive'$cred = New-Object Management.Automation.PSCredential -ArgumentList $user, $passNew-PSDrive $drv -PSProvider SqlServer -Root $root -Credential $cred -Scope 1Set-Location $drvBackup-SqlDatabase -ServerInstance $inst -Database $db -BackupFile $file
or you could backup the database by running an SQL statement via Invoke-SqlCmd
:
$backupFolder = '...'$additionToName = '...'$user = 'Username'$pass = ConvertTo-SecureString 'Password' -AsPlainText -Force$inst = 'Server\Instance'$db = 'master'$file = "$backupFolder${db}_db_$additionToName.bak"$sql = @"USE $db;GOBACKUP DATABASE $db TO DISK = '$file';GO"@Invoke-Sqlcmd -Query $sql -ServerInstance $inst –Username $user –Password $pass