How do I backup my mssql database using powershell

Question

I keep getting this error anytime i run my powershell script to backup my databases. I can't seem to figure out what the problem is. Any help will be most appreciated.

Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'MyServer'. "
At C:\Users\hp1\Desktop\scripts\backup.ps1:28 char:5
+     $smoBackup.SqlBackup($server)
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

My powershell script is shown below:

param(
    $serverName,
    $backupDirectory,
    $daysToStoreBackups
)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$dbs = $server.Databases
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })
{
    if($db.Name -ne "tempdb") #We don't want to backup the tempdb database 
     {
    $dbName = $database.Name

    $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
    $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"

    $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
    $smoBackup.Action = "Database"
    $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
    $smoBackup.BackupSetName = $dbName + " Backup"
    $smoBackup.Database = $dbName
    $smoBackup.MediaDescription = "Disk"
    $smoBackup.Devices.AddDevice($targetPath, "File")
    $smoBackup.SqlBackup($server)
    }
    #"backed up $dbName ($serverName) to $targetPath"
}

Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"

Does PowerShell not have any better error handling than this? Why all the SMO junk instead of simply Backup-SqlDatabase? — Aaron Bertrand
– Aaron Bertrand, Commented Mar 15, 2015 at 0:20

plasteezy · Accepted Answer · 2015-03-15 10:48:40Z

2

I found the problem. It had to do with permissions to the destination folder for the SQL Server Service Account. A common problem when backing up is checking that the SQL Server Service Account has the correct permissions to access the destination. PowerShell is just submitting T-SQL to SQL Server to execute, so the account running the script must have the requisite permissions. Once I gave it read/write everything worked well

answered Mar 15, 2015 at 10:48

plasteezy

3171 gold badge7 silver badges15 bronze badges

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

2 Comments

Ben Thul Over a year ago

In the general case, you can further examine the error by picking apart the exception. Luckily, Powershell stores all the exceptions in the $Error variable. One thing that I like to do is right after a script like this fails is $e = $error[0]; $e.Exception.InnerException and keep chaining calls to InnerException until I get something meaningful or until I run out.

No Name Over a year ago

Also if you are changing privileges to shared network folder ($backupDirectory) where you save backup file, not forget to restart your pc or you will continue to recieve errors like: Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server 'MYSERV'. ---> Microsoft.Sql Server.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or ba tch. ---> System.Data.SqlClient.SqlException: Cannot open backup device '\\FOLDER\foldernew\baza_2017_06_07_03_41.bak '. Operating system error 5(Access is denied.)

sai · Accepted Answer · 2016-01-12 14:28:03Z

0

mkdir D:\kings Invoke-Sqlcmd -Query "backup database Neelamsainaidu to disk='D:\kings\Neelammss.BAK'" -ServerInstance MSWORK-PC -Database Neelamsainaidu

answered Jan 12, 2016 at 14:28

sai

11

1 Comment

Martin Over a year ago

Please be a little more verbose.

Collectives™ on Stack Overflow

How do I backup my mssql database using powershell

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related