如何使用PowerShell备份我的mssql数据库 [英] How do I backup my mssql databases with powershell

问题描述

我一直收到这个错误：



异常调用带有1参数的SqlBackup：服务器'MyServer'的备份失败了。

在C：\ Users \ hp1 \Desktop\scripts\backup.ps1：28 char：5

+ $ smoBackup.SqlBackup（$ server）

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo：NotSpecified： （:) []，MethodInvocationException

+ FullyQualifiedErrorId：FailedOperationException



我的powershell脚本在下面

 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  |其中{$ _。IsSystemObject -eq  $ False }）
 {
 if（$ db.Name -ne   tempdb）#We don '  t想要备份tempdb数据库
 {
 $ dbName = $ database.Name 
 
 $ timestamp = Get-Date -format yyyy-MM-dd-HHmmss 
 $ targetPath = $ backupDirectory +\+ $ dbName +_+ $ timestamp +.bak
 
 $ smoBackup = New-Object（Microsoft.SqlServer.Managem ent.Smo.Backup）
 $ smoBackup.Action =数据库
 $ smoBackup.BackupSetDescription =完全备份+ $ dbName 
 $ smoBackup.BackupSetName = $ dbName +备份
 $ smoBackup.Database = $ dbName 
 $ smoBackup.MediaDescription =Disk
 $ smoBackup.Devices.AddDevice（$ targetPath，File）
 $ smoBackup.SqlBackup （$ server）
} 
＃将$ dbName（$ serverName）备份到$ targetPath
} 
 
 Get-ChildItem$ backupDirectory \ *。 bak|？ {$ _。lastwritetime -le（Get-Date）.AddDays（ -  $ daysToStoreBackups）} |％{Remove-Item $ _ -force} 
删除所有早于$ daysToStoreBackups天的备份 

解决方案

smoBackup.SqlBackup（

服务器）

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo：NotSpecified：（:) []，MethodInvocationException

+ FullyQualifiedErrorId：FailedOperationException



我的powershell脚本在下面

PARAM（

服务器名，

I keep getting this error:

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 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"

解决方案

smoBackup.SqlBackup(

server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException

My powershell script is below

param(

serverName,

这篇关于如何使用PowerShell备份我的mssql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！