首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Server备份脚本(PowerShell)工作在2016年服务器上,而不是2012年

问Server备份脚本(PowerShell)工作在2016年服务器上,而不是2012年
EN

Stack Overflow用户
提问于 2017-06-16 06:44:09
回答 1查看 730关注 0票数 0

我有以下脚本:

代码语言:javascript
复制
$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)上工作得很好,它没有,它给了我以下错误:

代码语言:javascript
复制
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

有什么办法绕过这件事吗?

提前感谢

-编辑

跑步时

代码语言:javascript
复制
$error[0] | fl -force

我得到以下信息:

代码语言:javascript
复制
System.Data.SqlClient.SqlException: Cannot open backup device '\\ServerAddress\Folder\file_2017-06-17-110451_daily.bak'. Operating system error 5(Access is denied.).
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-06-16 15:47:03

我在sql server 2012上测试了您的脚本,并成功地备份到本地文件夹。

你的剧本运行得很好。

尝试(用于测试)备份到服务器上的本地文件夹。

确保:

  • 您为Microsoft 2012安装了Microsoft扩展(与SMO一起)。如需详细信息,请阅读我的回答:What do I need to execute a SQL Server PowerShell module
  • 您的服务器对共享文件夹“\backup\ server 1”具有访问权限。在服务器2012中,在共享文件夹“\backup\ server 1”上设置MSSQLSERVER帐户的权限R/W

编辑:

从您的注释中,通过使用本地文件夹,脚本运行良好。因此,问题在于共享文件夹的设置。

工作组环境中的

如果您的sql server 2012正在工作组环境中工作:

  • 在sql server 2012和备份服务器(使用计算机管理器)上使用相同的密码(例如“sql2012”)创建相同的窗口用户帐户。
  • 将此帐户( sql 2012/password)设置为运行Sql服务的帐户(使用Server配置管理器工具)。
  • 在备份服务器中,将共享文件夹中的完全R/W权限设置为帐户sql2012

域环境中的

  • 使用域帐户作为运行sql服务的帐户(使用Server配置管理器工具)
  • 在备份服务器中,将共享文件夹中的完全R/W权限设置为该域帐户。
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/44582535

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档