Use Backup-SqlDatabase cmdlet with SQL Server Authentication

Question

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.

Can you run the script as a Powershell job step in the SQL Server instance? Then it would run under the Agent's account and authentication shouldn't be a problem. — Bill Hurt
– Bill Hurt, Commented Dec 8, 2013 at 1:03
As a side note: the use of subexpressions in your backup filename is overkill. You can separate a variable name from a following underscore by putting the name in curly brackets: "$backupFolder${dbname}_db_$addinionToName.bak". — Ansgar Wiechers
– Ansgar Wiechers, Commented Dec 8, 2013 at 11:01

Chad Miller · Accepted Answer · 2013-12-09 20:50:29Z

12

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.

answered Dec 9, 2013 at 20:50

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hosjay Over a year ago

Hey @chad-miller, thanks for your reply. I also need this. However, since our backup process is scheduled to run every night in our server and it is not done by a human, I was wondering if we can include password somehow in the script so that it doesn't wait for a human to enter a password...

Ansgar Wiechers · Accepted Answer · 2013-12-08 16:53:02Z

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

Collectives™ on Stack Overflow

Use Backup-SqlDatabase cmdlet with SQL Server Authentication

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related