Is there a way to call Backup-SqlDatabase cmdlet but have it connect to SQL Server 2012 with SQL Server credentials?
The command I am currently using is:
Backup-SqlDatabase -ServerInstance $serverName -Database $sqldbname -BackupFile "$($backupFolder)$($dbname)_db_$($addinionToName).bak"
But this relies on the user, under which it is being call, and by default, Windows Authentication is being used.
You can get to SQL Provider by opening the PowerShell ISE and running Import-Module SqlServer or by right-clicking on the Databases node of a SQL instance in Object Explorer of SSMS. Once here you can backup a single database by typing Backup-SqlDatabase and then the name of the database you want to backup.
The Backup-SqlDatabase cmdlet performs backup operations on a SQL Server database. This includes full database backups, transaction log backups, and database file backups. This cmdlet is modeled after the Microsoft.
By default, the SQL Server PowerShell components use Windows Authentication when connecting to an instance of the Database Engine. You can use SQL Server Authentication by either defining a PowerShell virtual drive, or by specifying the -Username and -Password parameters for Invoke-Sqlcmd.
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, $pass
New-PSDrive $drv -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
Set-Location $drv
Backup-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;
GO
BACKUP DATABASE $db TO DISK = '$file';
GO
"@
Invoke-Sqlcmd -Query $sql -ServerInstance $inst –Username $user –Password $pass
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With