快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

使用SQL Server 2012 PowerShell cmdlet备份和还原SQL Server

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

在很多情况下,在PowerShell中进行备份和还原脚本很有意义。正如艾伦(Allen)所展示的那样,微软已努力使其变得更加容易。

在谈论数据库管理员的最重要任务时,您会听到的最常见的笑话是这样的– DBA需要以下两项之一,即良好的备份或良好的简历。这是真的。如果您不执行备份,并确保可以从这些备份中恢复数据库,那么您和您的公司都将面临数据丢失的风险。

在BACKUP DATABASETransact-SQL命令已经存在了很长一段时间(由之前DUMP DATABASE 对于我们这些身边的SQL Server足够长的时间来记住它。命令),它仍然得到你的数据库保护的一个伟大的方式,而是帮助我们走向更自动化的移动完成日常工作的方法,现在还有其他可用方法。在本文中,我将通过实际示例描述如何通过SMO和SQLPS从PowerShell有效地编写备份脚本,以及如何使用SQL Server 2012引入的PowerShell Cmdlet大大简化脚本编写过程。

在SQL Server 2012中,Microsoft为备份和还原添加了四个新的cmdlet:

  • Backup-SqlDatabase
  • Restore-SqlDatabase
  • Backup-ASDatabase
  • Restore-ASDatabase

为什么考虑脚本编写来自外部进程的备份。

当备份过程变得更加复杂时,需要完成越来越多的文件系统工作。这些作业可能需要将备份命名和安排到目录中,删除不再需要的旧备份,将它们复制到异地,检查备份完整性,写入日志等。您可能需要在一个操作中备份多个数据库,甚至在不同的服务器上,或将数据库复制到多个服务器或VM进行测试。在某些时候,需要考虑脚本化的备份和还原,因此PowerShell是理想的选择。

使用PowerShell和SMO编写脚本

2009年,我为Microsoft发布了一份白皮书,其中使用服务器管理对象(SMO)库引入了一个名为backup.ps1的PowerShell脚本来备份数据库,并且由于SQL Server 2000至2012版本支持SMO,因此该脚本仍然有效。(了解和使用SQL Server 2008中的PowerShell支持)实际上,Microsoft在SQL Server 2012的SMO领域中没有添加很多功能,因此没有太多要学习的东西。

在白皮书中,我讨论了用于PowerShell的SQL Server管理单元以及SQL Server 2008和SQL Server 2008 R2中包含的SQLPS.exe“微型外壳”。尽管这是在引入SQL Server 2008时PowerShell团队规定的扩展PowerShell的方式,但是技术已发生变化,并且PowerShell团队现在为扩展PowerShell推广了“模块”模型。因此,在安装SQL Server 2012之前,PowerShell 2.0版是服务器上的最低要求,并且SQLPS.exe程序已由名为–等待– SQLPS的新模块替换。

Microsoft在SQLPS模块中提供了许多新的cmdlet,其中大多数特定于管理可用性组和高可用性/灾难恢复。但是,除了这些,他们提供了四个新的cmdlet特定的备份和恢复操作- ,,Backup-SqlDatabase 和。Restore-SqlDatabaseBackup-ASDatabaseRestore-ASDatabase

 安装SQLPS模块

当启动PowerShell并想使用SQLPS模块时,需要使用Import-Modulecmdlet。从PowerShell版本2.0开始,Microsoft在导入过程中根据批准的动词列表检查对象名称。由于动词“备份和还原”不在批准列表中,因此您将收到一条消息,指示模块中存在未批准的动词。通过DisableNameChecking在导入SQLPS模块时在Import-Module cmdlet中包括– 参数,可以避免此错误。(请注意,当您导入SQLPS模块时,您的位置将被设置为SQL Server提供程序的根。这是预期的行为。)

使用Backup-SqlDatabase CmdLet备份数据库

 使用Backup-SQLDatabase进行简单备份

加载模块后,您可以使用以下命令轻松为数据库创建备份:

{$dt = GetDate Format yyyyMMddHHmmss

$dbname = ‘AdventureWorks’

BackupSqlDatabase ServerInstance TESTSQL Database $dbname BackupFileE:\Backup\$($dbname)_db_$($dt).bak

当然,有几种方法可以调用此cmdlet。另一个方法是利用提供程序,导航到SQL Server实例下的Databases目录,并使用Get– ChildItem cmdlet遍历数据库以备份每个数据库。因为您已经连接到实例,所以不需要– ServerInstance参数。

简单地说,如果您是,则只需键入

SetLocation SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases

getchilditem|BackupSqlDatabase

..它将备份到默认的备份目录,并使用数据库的名称作为备份文件的名称。如果需要指定备份文件的名称或许多可能的参数中的任何其他参数,则可能需要这样做。

SetLocation SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases

foreach ($db in (GetChildItem))

    {

        $dbname = $db.Name

        $dt = GetDate Format yyyyMMddHHmmss

        BackupSqlDatabase Database $dbname BackupFile$($dbname)_db_$($dt).bak

    }

这会将每个用户数据库备份到默认备份目录,并在备份文件名中使用数据库名称以及备份的当前日期和时间。(请注意,您可以在循环中将– Force参数与Get– ChildItem cmdlet一起使用foreach以包括系统数据库,但只需确保过滤掉tempdb数据库即可。)

getchilditem force|where name ne ‘TempDB’| BackupSqlDatabase

您也可以创建一个包含SMO Server对象的变量,并使用– InputObject参数而不是– ServerInstance对象。尽管我们将显示单个数据库发生的情况,但是当您需要备份不同服务器中的数据库列表时,这将很有用。

$dt = GetDate Format yyyyMMddHHmmss

$dbname = ‘AdventureWorks’

$svr =  newobject (‘Microsoft.SqlServer.Management.Smo.Server’) ‘TESTSQL’

BackupSqlDatabase InputObject $svr Database $dbname BackupFileE:\Backup\$($dbname)_db_$($dt).bak

或者,可以将包含SMO数据库对象的变量用于目标数据库,并使用-DatabaseObject参数。

$dt = GetDate Format yyyyMMddHHmmss

SetLocation SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases\AdventureWorks

$db = GetItem .

$dbname = $db.Name

BackupSqlDatabase DatabaseObject $db BackupFileE:\Backup\$($dbname)_db_$($dt).bak

在每个示例中,我仅使用了最少的参数即可使示例保持简单。在您的代码中,您应该查看其他参数以获得适合您需求的正确备份解决方案。那么这些参数是什么,应该如何使用?

 SQL备份:关键参数

我将始终包含的参数是– BackupAction。可能的值为Database,Files或Log,默认值(如果未包括)为Database。如果要执行差异备份,则将BackupActionas 指定为Database,然后添加-Incremental参数。如果您要备份单个文件或文件组,则可以使用“文件”选项,并且当然可以使用“日志”选项进行事务日志备份。为了节省空间,这些示例排除了该参数,因此它们创建了完整的数据库备份。

例如,我将始终包括 –CompressionOption On参数。我还要确定要包括该-ConnectionTimeout参数并将其设置为0值,因为您不希望您的备份终止,因为在备份发生时脚本和SQL Server之间的连接是空闲的。

如果我们从该Backup-SqlDatabasecmdlet的get-help输出中扩展该cmdlet 的参数列表,则会得到以下列表:

备份数据库

[-数据库] <string>

[-BackupFile] <string []>]

[-ServerInstance <string []>]

[-BackupAction <BackupActionType>]

[-BackupDevice <BackupDeviceItem []>]

[-BackupSetDescription <字符串>]

[-BackupSetName <字符串>]

[-BlockSize <int>]

[-BufferCount <int>]

[-校验和]

[-CompressionOption <BackupCompressionOptions>]

[-ConnectionTimeout <int>]

[-ContinueAfterError]

[-仅复制]

[-凭据<PSCredential>]

[-DatabaseFile <string []>]

[-DatabaseFileGroup <字符串[]>]

[-ExpirationDate <日期时间>]

[-FormatMedia]

[-增加的]

[-初始化]

[-LogTruncationType <BackupTruncateLogType>]

[-MaxTransferSize <int>]

[-MediaDescription <字符串>]

[-MediaName <字符串>]

[-MirrorDevices <BackupDeviceList []>]

[-NoRecovery]

[-不倒带]

[-Passthru]

[-重新开始]

[-RetainDays <int>]

[-脚本]

[-SkipTapeHeader]

[-UndoFileName <字符串>]

[-UnloadTapeAfter]

[-确认]

[-如果]

[<CommonParameters>]

与SMO相比的优势

对我来说很明显,他们已经在此cmdlet中添加了SMO中可用功能的功能。可以通过Transact-SQL获得并且SMO缺少的功能是可以设置备份的块大小和缓冲区数,并且它们已经启用了这些属性。以下是通过SMO备份对象提供给我们的等效属性和方法:

(这是从加载了 SMOExtended DLL(备份和还原对象所在的位置)之后的Visual Studio中的对象浏览器中获得的。)

使用Restore-SQLDatabase CmdLet还原数据库

我们需要还原数据库的原因有很多,因此与还原相比,还原的选项更多。

演示还原的最简单方法是简单地从完整备份还原数据库,并设置选项以覆盖现有数据库。

RestoreSqlDatabase ServerInstance TESTSQL Database AdventureWorks `

BackupFileE:\Backup\AdventureWorks_db_20130420153024.bakReplaceDatabase

我不得不频繁还原数据库的原因之一是从某个用户无意删除的表中恢复数据,但是随后进行了其他事务更改,这些更改不能丢失。为此,我将备份还原到同一服务器上的另一个命名数据库,通常使用原始数据库名称,并将备份日期附加在名称上。然后,我可以将备份中丢失的数据直接复制到原始数据库的表中,并在一切恢复正常后将其删除。

使用SMO还原数据库

这是我们直接使用SMO进行操作的方法。

# Connect to the specified instance

$srv = newobject (‘Microsoft.SqlServer.Management.Smo.Server’) ‘TESTSQL’

# Get the default file and log locations

# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)

$fileloc = $srv.Settings.DefaultFile

$logloc = $srv.Settings.DefaultLog

if ($fileloc.Length eq 0) {

    $fileloc = $srv.Information.MasterDBPath

    }

if ($logloc.Length eq 0) {

    $logloc = $srv.Information.MasterDBLogPath

    }

# Identify the backup file to use, and the name of the database copy to create

$bckfile = ‘E:\Backup\AdventureWorks_db_20101016135438.bak’

$dbname = ‘AdventureWorks_20101016’

# Build the physical file names for the database copy

$dbfile = $fileloc + ‘\’+ $dbname + ‘_Data.mdf’

$logfile = $logloc + ‘\’+ $dbname + ‘_Log.ldf’

# Use the backup file name to create the backup device

$bdi = newobject (‘Microsoft.SqlServer.Management.Smo.BackupDeviceItem’) ($bckfile, ‘File’)

# Create the new restore object, set the database name and add the backup device

$rs = newobject(‘Microsoft.SqlServer.Management.Smo.Restore’)

$rs.Database = $dbname

$rs.Devices.Add($bdi)

# Get the file list info from the backup file

$fl = $rs.ReadFileList($srv)

foreach ($fil in $fl) {

    $rsfile = newobject(‘Microsoft.SqlServer.Management.Smo.RelocateFile’)

    $rsfile.LogicalFileName = $fil.LogicalName

    if ($fil.Type eq ‘D’){

        $rsfile.PhysicalFileName = $dbfile

        }

    else {

        $rsfile.PhysicalFileName = $logfile

        }

    $rs.RelocateFiles.Add($rsfile)

    }

# Restore the database

$rs.SqlRestore($srv)

将现有数据库的副本还原到同一服务器时,潜在的问题之一是物理名称冲突,并解决了您使用SMO RelocateFile对象的问题。这等效于在Transact-SQL中使用WITH MOVE子句。有趣的是,这些Restore-SqlDatabasecmdlet需要这些相同的SMO对象才能完成相同的任务。

使用纯SMO和使用Restore-SqlDatabasecmdlet完成此操作之间的唯一区别是存储和传递RelocateFile对象的方式。我们需要创建一个空集合,在读取备份文件列表之后立即执行此操作,然后将每个RelocateFile对象添加到该集合中,然后调用该Restore-SqlDatabasecmdlet。这只是相同代码的最后一部分,而是使用新的cmdlet。

# Get the file list info from the backup file

$fl = $rs.ReadFileList($srv)

$rfl = @()

foreach ($fil in $fl) {

    $rsfile = newobject(‘Microsoft.SqlServer.Management.Smo.RelocateFile’)

    $rsfile.LogicalFileName = $fil.LogicalName

    if ($fil.Type eq ‘D’) {

        $rsfile.PhysicalFileName = $dbfile

        }

    else {

        $rsfile.PhysicalFileName = $logfile

        }

    $rfl += $rsfile

    }

# Restore the database

RestoreSqlDatabase ServerInstance TESTSQL Database $dbname `

 BackupFileE:\Backup\AdventureWorks_db_20101016135438.bak`

 RelocateFile $rfl

在这种情况下,我会坚持使用纯SMO方法,因为我认为它更干净。

将数据库还原到某个时间点

我将分享的最后一个示例涉及将数据库还原到某个时间点。有时,导致您还原的问题是您所知道的一次发生的事情,并且您可以决定将数据库恢复到那个时候。

在这种情况下,我们将所有备份文件存储在本地服务器上的E:\ Backup目录中。我们有多个完整备份,多个差异备份和多个事务日志备份,包括在目标时间点之后进行的日志备份。文件名遵循惯例DatabaseName_type_datetime.ext,其中type是db,diff或tran,而ext是bak或trn。

$dbname = ‘AdventureWorks’

$restorept = ‘2013-04-20 15:30:00’

SetLocation ‘E:\Backup’

$fullfile = GetChildItem Filter$($dbname)_db_*| WhereObject {$_.LastWriteTime lt $restorept} | SortObject LastWriteTime Desc | SelectObject First 1

$difffile = GetChildItem Filter$($dbname)_diff_*| WhereObject {$_.LastWriteTime lt $restorept} | SortObject LastWriteTime Desc | SelectObject First 1

$tranfile = GetChildItem Filter$($dbname)_tran_*| WhereObject {$_.LastWriteTime gt $difffile.LastWriteTime} | SortObject LastWriteTime Asc

现在,$fullfile 变量包含目标时间之前最后一次完整备份$difffile的文件信息,变量包含目标时间之前最后一次差异备份的文件信息, $tranfile变量包含自差异备份以来进行的所有事务日志备份的文件信息在 $difffile.

首先,我们需要使用replace选项还原完整备份,并指定不进行恢复。

RestoreSqlDatabase ServerInstance TESTSQL Database $dbname `

BackupFile $fullfile.FullName ReplaceDatabase `

NoRecovery

接下来,我们恢复最新的差异,再次指定不恢复。

RestoreSqlDatabase ServerInstance TESTSQL Database $dbname `

BackupFile $difffile.FullName ReplaceDatabase `

NoRecovery

最后,我们通过遍历$tranfile变量中的文件来还原事务日志备份。如果该LastWriteTime属性小于我们的$restorept变量,请恢复而无法恢复并继续进行下一个。在还原点之后进行的第一个日志备份是使用-ToPointInTime不带-NoRecovery 参数的参数进行还原的,并且设置了指示器以防止我们尝试还原更多的事务日志备份。

$recovery = 0

foreach ($trnfile in $tranfile) {

     if ($trnfile.LastWriteTime lt $restorept) {

         RestoreSqlDatabase ServerInstance TESTSQL Database $dbname `

BackupFile $trnfile.FullName ReplaceDatabase `

NoRecovery

         }

    else {

         if ($recovery eq 0) {

             RestoreSqlDatabase ServerInstance TESTSQL Database $dbname `

BackupFile $trnfile.FullName ReplaceDatabase `

ToPointInTime $restorept

$recovery = 1

            }

        }

    }

这里有很多用途,直接访问操作系统功能可以使这些备份和恢复自动化,而不是尝试通过Transact-SQL进行操作。

仅举例来说,以下是Restore对象的SMO属性和方法:

相应地,这是来自Restore-SqlDatabasecmdlet 的帮助文本,其中显示了参数:

Restore-SqlDatabase

[-数据库] <string>

[[-BackupFile] <string []>]

-ServerInstance <string []>

[-BackupDevice <BackupDeviceItem []>]

[-BlockSize <int>]

[-BufferCount <int>]

[-校验和]

[-ClearSuspectPageTable]

[-ConnectionTimeout <int>]

[-ContinueAfterError]

[-凭据<PSCredential>]

[-DatabaseFile <string []>]

[-DatabaseFileGroup <字符串[]>]

[-FileNumber <int>]

[-KeepReplication]

[-MaxTransferSize <int>]

[-MediaName <字符串>]

[-NoRecovery]

[-不倒带]

[-Offset <Int64 []>]

[-部分]

[-Passthru]

[-RelocateFile <RelocateFile []>]

[-ReplaceDatabase]

[-重新开始]

[-RestoreAction <RestoreActionType>]

[-RestrictedUser]

[-脚本]

[-StandbyFile <string>]

[-StopAtMarkAfterDate <字符串>]

[-StopAtMarkName <字符串>]

[-StopBeforeMarkAfterDate <字符串>]

[-StopBeforeMarkName <字符串>]

[-ToPointInTime <字符串>]

[-UnloadTapeAfter]

[-确认]

[-如果]

[<CommonParameters>]

使用Backup-ASDatabase备份Analysis Services

即使有很少的选择,也需要备份Analysis Services数据库。您需要做的第一件事是加载Analysis Services cmdlet,因为它们在不同的模块中。

ImportModule SQLASCMDLETS

让我们看一下Backup-ASDatabasecmdlet 的参数。

备份AS数据库

[-BackupFile] <字符串>

[-名称] <string>

[-AllowOverwrite <SwitchParameter>]

[-BackupRemotePartitions <SwitchParameter>]

[-ApplyCompression <SwitchParameter>]

[-FilePassword <SecureString>]

[-位置<Microsoft.AnalysisServices.BackupLocation []>]

[-服务器<string>]

[-凭据<PSCredential>]

[<CommonParameters>]

请注意,这里没有选项可以进行差异日志或事务日志备份。AWDB数据库的简单备份如下所示。

BackupASDatabaseE:\Backup\AWDB.abfAWDB

如果要覆盖已存在的备份,则可以添加-AllowOverwrite  参数,如果要压缩备份(总是一个好主意),则可以添加-ApplyCompression参数,如果要加密备份,则可以添加-FilePassword参数。

使用Restore-ASDatabase还原Analysis Services数据库

同样,当我们要还原Analysis Services数据库时,没有太多选择。

Restore-ASDatabase

[-RestoreFile] <字符串>

[-名称] <System.String>

[-AllowOverwrite <SwitchParameter>]

[-位置<Microsoft.AnalysisServices.RestoreLocation []>]

[-安全性<Microsoft.AnalysisServices.RestoreSecurity>]

[-密码<System.SecureString>]

[-StorageLocation <System.String>]

[-服务器<string>]

[-凭据<PSCredential>] [<CommonParameters>]

要还原AWDB数据库,我们可以这样做。

RestoreASDatabaseE:\Backup\AWDB.abfAWDB Security:CopyAll

这将还原数据库,并从备份中还原角色和成员。

摘要

备份和还原是管理任何组织数据的关键活动。通常,最好使用PowerShell和SMO编写用于管理SQL Server的备份和还原任务的脚本,特别是在DBA需要执行更复杂,可重复的工作时。例如,在必须高度可用的数据库的情况下,优良作法是创建用于还原数据库的脚本,对其进行彻底的测试,然后将其用于演练。为此,SQL Server 2012附带的Backup and Restore cmdlet是使任务尽可能简单的理想选择。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638