我有以下脚本:
$serverName = "."
$backupDirectory = "\\backup\server1"
$daysToStoreDailyBackups = 7
$daysToStoreWeeklyBackups = 28
$monthsToStoreMonthlyBackups = 3
[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
$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$serverName
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = "sa"
$mySrvConn.Password = "myPass"
$server = new-object Microsoft.SqlServer.Management.SMO.Server($mySrvConn)
$dbs = $server.Databases
$startDate = (Get-Date)
"$startDate"
Get-ChildItem "$backupDirectory\*_daily.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreDailyBackups)} |% {Remove-Item $_ -force }
"removed all previous daily backups older than $daysToStoreDailyBackups days"
foreach ($database in $dbs | where {$_.IsSystemObject -eq $False})
{
$dbName = $database.Name
if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
{
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_daily.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"
}
else
{
"$dbName backup skipped"
}
}
if([Int] (Get-Date).DayOfWeek -eq 0)
{
Get-ChildItem "$backupDirectory\*_weekly.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreWeeklyBackups)} |% {Remove-Item $_ -force }
"removed all previous daily backups older than $daysToStoreWeeklyBackups days"
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
$dbName = $database.Name
if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
{
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_weekly.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"
}
else
{
"$dbName backup skipped"
}
}
}
if([Int] (Get-Date).Day -eq 1)
{
Get-ChildItem "$backupDirectory\*_monthly.bak" |? { $_.lastwritetime -le (Get-Date).AddMonths(-$monthsToStoreMonthlyBackups)} |% {Remove-Item $_ -force }
"removed all previous monthly backups older than $monthsToStoreMonthlyBackups days"
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
$dbName = $database.Name
if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
{
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_monthly.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"
}
else
{
"$dbName backup skipped"
}
}
}这在我的一个服务器(运行服务器2016)和另一个服务器(运行服务器2012)上工作得很好,它没有,它给了我以下错误:
Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'Server1'. "
At C:\Users\Administrator\Documents\Scripts\sqlFullBackup.ps1:43 char:9
+ $smoBackup.SqlBackup($server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException有什么办法绕过这件事吗?
提前感谢
-编辑
跑步时
$error[0] | fl -force我得到以下信息:
System.Data.SqlClient.SqlException: Cannot open backup device '\\ServerAddress\Folder\file_2017-06-17-110451_daily.bak'. Operating system error 5(Access is denied.).发布于 2017-06-16 15:47:03
我在sql server 2012上测试了您的脚本,并成功地备份到本地文件夹。
你的剧本运行得很好。
尝试(用于测试)备份到服务器上的本地文件夹。
确保:
编辑:
从您的注释中,通过使用本地文件夹,脚本运行良好。因此,问题在于共享文件夹的设置。
工作组环境中的
如果您的sql server 2012正在工作组环境中工作:
域环境中的
https://stackoverflow.com/questions/44582535
复制相似问题