65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 2000/2005：自动化数据库备份与还原

Neeraj Kr
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.76/5 (19投票s)

2008年2月19日

CDDL

10分钟阅读

viewsIcon

69407

提供关于创建自动化备份和还原过程的见解。

SQL Server：自动化数据库备份与还原

 

引言

数据库服务器是企业信息存储的核心，因此必须特别注意管理这一关键资源。一旦出现问题，必须快速、高效、正确地解决，否则组织将面临重大损失。

 

在我工作过的所有环境中，虽然有些可能被我轻视，但备份显然不是其中之一。在我看来，数据库备份的重要性仅次于数据本身。怀着这样的心态，我坚决确保备份能够正确地、按计划进行。

 

尽管这并不有趣，但作为 DBA，执行备份是最重要的任务之一。为了减轻将多个服务器上的每日手动备份和还原推送到数据恢复服务器的负担，我为此创建了一个自动化流程，撰写本教程的唯一目的是帮助那些每天为此奋斗的人。

 

先决条件

最有趣的一点是，你可以从 SQL Server 内部运行 DOS 命令。当我提到运行 DOS 命令时，这显然也包括运行批处理文件。

 

  • 文件夹
    为了实现自动备份和还原，请在您的“C”驱动器上创建两个文件夹，并将它们命名为“DataBackup”和“DBRepository”。“DataBackup”文件夹将用作初始备份的存放文件夹，而“DBRepository”文件夹将用于存储二次备份。如果存在其他驱动器，“DBRepository”文件夹也可以创建在其他驱动器上。

    在我这里，我在“D”驱动器上创建了“DBRepository”文件夹。

  • Winzip 应用程序
    如果需要将数据库还原到其他服务器（例如备用服务器），则需要授权版本的“Winzip 10.0”或更高版本。虽然您可以直接将备份文件 FTP 到数据恢复服务器，但如果备份文件很大，您应该在 FTP 到另一台服务器之前先压缩备份文件。这样会更快。

  • wzcline22.exe
    下载并安装上述文件。此文件允许从命令提示符运行 zip 命令，从而也可以从批处理文件中运行。请记住，您的服务器上必须安装授权版本的 winzip 10.0 或更高版本，否则它将无法工作。

    您可以从以下位置下载此文件
    http://www.winzip.com/dprob.htm

  • Push.bat
    现在，您需要创建一个批处理文件，其中包含以下代码行
    CD\
    cd DataBackup
    "c:\program files\winzip\wzzip.exe" DBFull.zip sg_Complete_Backup.bak
    "c:\program files\winzip\wzzip.exe" DBDiff.zip sg_Differential_Backup.bak
    c:\windows\system32\FTP.exe -s:AccessFTP.txt
    move *.bak D:\DBRepository
    del *.zip
    将此批处理文件命名为“Push.bat”并保存在“DataBackup”文件夹中。这是自动化备份最初的存放文件夹。

    上面写的代码行只是普通的 DOS 命令，您应该已经熟悉了。

    在我的例子中，我将完整备份命名为“sg_complete_backup.bak”，将差异备份命名为“sg_differential_backup.bak”。另外，完整备份将压缩为“DBFull.zip”，差异备份将压缩为“DBDiff.zip”。

    我创建了一个批处理文件，该文件同时适用于完整和差异备份集。

    注意：在命名文件时需要谨慎，因为这是一个自动化过程，更改一个文件名将需要在所有提及该文件名的位置进行相应更改。

  • AccessFTP.txt
    如果您查看上面创建的批处理文件代码，您会发现该批处理文件打开了“AccessFTP.txt”文件，用于将 zip 文件 FTP 到数据恢复服务器。因此，现在创建一个文本文件，并将其命名为“AccessFTP.txt”，保存在“DataBackup”文件夹中，与“Push.bat”放在一起。在该文件中写入以下行：

    open
    <<数据恢复服务器的 IP 地址>>
    <<数据恢复服务器的 Windows 登录用户名>>
    <<数据恢复服务器的 Windows 登录密码>>
    cd <<您要在数据恢复服务器上 FTP zip 文件的文件夹>>
    Put "C:/DataBackup/DBFull.zip"
    Put "C:/DataBackup/DBDiff.zip"
    quit
    我将逐行解释上述代码。

    • open – 这将要求您输入要连接的机器的 IP 地址。

    • <<数据恢复服务器的 IP 地址>> - 这是数据恢复服务器的 IP 地址。

    • <<数据恢复服务器的 Windows 登录用户名>> - 数据恢复服务器的 Windows 用户名。

    • <<数据恢复服务器的 Windows 登录密码>> - 数据恢复服务器的 Windows 密码。

    • cd <<您要在数据恢复服务器上 FTP zip 文件的文件夹>> - 这可能是 FTP 根文件夹或数据恢复服务器上配置的 FTP 文件夹内的文件夹。

    • Put "C:/DataBackup/DBFull.zip" – 这会将当前服务器上的完整备份集传输到数据恢复服务器上的上述文件夹。

    • Put "C:/DataBackup/DBDiff.zip" – 这与上面相同，但适用于差异备份集。

    • Quit – 这将退出文本文件。

 

实施
现在，在满足了上述所有先决条件后，是时候深入了解实施部分了。大家都应该知道，为了让计划程序运行，SQL Server Agent 必须处于启动状态，即必须正在运行。

 

由于我们需要一个自动化流程；我们将把整个流程安排为一个作业，以便根据我们设定的时间运行它。为此，SQL Server Agent 必须正在运行。

 

按照以下步骤创建作业，该作业将执行完整备份，压缩备份文件并将之 FTP 到数据恢复服务器。此外，这些步骤还包括运行数据恢复服务器上创建的作业的命令。

  • 步骤 1
    • 展开 SQL Server Agent 以查看作业。
    • 在出现的向导中，在“Name”文本框中输入计划程序的名称。

 

  • 第二步
    • 点击“Steps”选项卡，然后点击“New”按钮以创建一个新步骤。

 

    • 在文本框中输入步骤名称。在“Database name”下拉列表框中，将数据库名称保留为“master”。

      将数据库名称保留为“master”的唯一目的是，我们将使用 master 数据库中提供的扩展存储过程来完成我们的全部任务。

    • 在“Command”标签前的文本框中写入代码。请注意，备份路径指定为“c:\DataBackup\sg_complete_backup.back”。这与我们创建批处理文件和文本文件的路径相同。



    • 将数据库名称“SG”替换为您自己的数据库名称。此外，您还需要将备份文件名更改为您在上面创建的批处理文件中更改的名称。

    • 点击“Parse”按钮以确保所写的命令没有错误。点击“Apply”按钮，然后点击“OK”退出此对话框。

    • 现在是创建作业的第二个步骤。此步骤将利用 master 数据库的扩展存储过程“xp_cmdshell”来运行上面创建的批处理文件中的 DOS 命令。

      此步骤主要负责创建备份文件的 zip 文件以及将其 FTP 到指定的数据恢复服务器。

      请注意，我再次将数据库服务器的默认名称保留为“master”。



    • 点击“Parse”检查错误，点击“Apply”，然后点击“OK”退出此步骤。

    • 现在创建一个新步骤，该步骤将启动在远程数据恢复服务器上解压缩和还原数据库的作业。

      由于此步骤有几行代码，屏幕截图未显示完整代码。因此，完整代码写在图像下方。

 

declare @retcode int
declare @job_name varchar(300)
declare @server_name varchar(200)
declare @query varchar(8000)
declare @cmd varchar(8000)



set @job_name = '<<这是您要运行以解压缩和还原数据库的数据恢复服务器的作业名称>>' ------------------在此处输入作业名称。
set @server_name = '<<数据恢复服务器的 IP 地址>>' ------------------在此处输入服务器名称。



set @query = 'exec msdb.dbo.sp_start_job @job_name = ''' + @job_name + ''''


set @cmd = 'osql /U <<数据库用户名>> /P <<数据库密码>> -S ' + @server_name + ' -Q "' + @query + '"'


exec @retcode = master.dbo.xp_cmdshell @cmd

 

 

  • 步骤 3
    • 打开数据恢复服务器，然后打开 SQL Enterprise Manager。

    • 现在按照“步骤 2”下所述的初始步骤创建一个新作业。

    • 给新作业起与您在“步骤 2”的最后一步中给出的名称相同的名称，即此作业的名称必须与上面用于解压缩和还原数据库的作业的最后一步中提到的名称相同。

      如果名称不匹配，则整个服务将无法正常工作。



    • 现在点击“Steps”选项卡，然后点击“New”按钮以在此数据恢复服务器上为此新作业创建步骤。

    • 在第一个步骤中，写入运行批处理文件的命令，该批处理文件将解压缩数据库备份并删除 zip 文件。

      在创建完解压缩和还原备份数据库的作业后，我稍后将对此批处理文件进行简要说明。



    • 点击“OK”按钮应用并退出对话框。现在再次点击“New”按钮以创建第二个步骤。

      这是主要步骤，负责还原数据库



declare @str nvarchar(4000)
declare @spid int

while exists (select spid from sysprocesses where dbid= db_id('<<数据库名称>>'
begin
set @spid = (select min(spid) from sysprocesses where dbid= db_id('<<数据库名称>>'))
set @str= 'kill ' + convert(nvarchar,@spid)
execute sp_executesql @str

end


RESTORE DATABASE <<数据库名称>> from disk=’<<数据恢复服务器上备份文件所在路径>>'
with StandBy='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\UNDO_<<数据库名称>>.DAT',
Move '<<逻辑数据文件名称>>' To '<<物理数据文件所在路径>>',
Move '<<逻辑日志文件名称>>' To '<<物理日志文件所在路径>>',
替换

 

 

    • 在我的例子中，我将数据库设置为只读。因此，我在还原中使用了“StandBy”命令。您也可以使用“NoRecovery”选项。

    • 现在创建第三个步骤，该步骤负责从数据库还原的地点删除备份文件。在数据库已还原并且为了节省磁盘空间，我们也可以删除备份文件。

      删除备份文件也将通过运行批处理文件来完成。



  • 步骤 4
    现在是时候创建批处理文件了，这些批处理文件将从上述步骤运行，并负责解压缩 zip 文件、删除 zip 文件，以及在成功还原后删除备份文件。

    • 创建一个包含以下代码行的批处理文件



      D
      cd "ftp/db backup/SE"
      "c:\program files\winzip\WZUNZIP.EXE" DBFull.zip
      del*.zip
      将此批处理文件命名为您在“步骤 3”中创建的作业的第一步中提到的名称。

    • 此文件将解压缩数据库并删除 zip 文件。

    • 再创建一个包含以下代码行的批处理文件

      D
      cd "ftp/db backup/SE"
      del *.bak

      将此批处理文件命名为您在“步骤 3”中创建的作业的最后一步中提到的名称。

    • 这将会在成功还原后删除备份文件。

    • 这两个批处理文件都必须保存在备份文件被 FTP 到的文件夹中，即数据恢复服务器的 FTP 文件夹。

 

 

您现在可以安排完整/差异备份的作业。

这完成了数据库备份和还原自动化过程。希望这对大家有很大帮助。如果您遇到问题、有疑问或需要澄清，请随时给我发送邮件至 neerajks77@gmail.com。

 

 

© . All rights reserved.