• SQL Server的备份和恢复


    作者:朱金灿

    来源:http://blog.csdn.net/clever101

     

             在上篇博客中谈了Oracle10g的备份和恢复,今天谈了SQLServer的备份和恢复。和Oracle10g不一样,SQLServer的备份和恢复只有两个级别:数据库级别(针对指定数据库)和记录表级别(针对指定表)。

     

    1.     数据库的备份和恢复

            SQL Server的数据库备份并没有对应的工具程序来进行,而是通过执行TranscatSQL的BACKUP DATABASE语句来进行数据库备份。BACKUP DATABASE的语法如下:

    BACKUP DATABASE { database_name | @database_name_var } 
      TO <backup_device> [ ,...n ] 
      [ <MIRROR TO clause> ] [ next-mirror-to ]
      [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
    [;]
    

          一个简单的例子是:

    BACKUP DATABASE  TechInfo  TO DISK = 'E:\Bak\db.bak'
    -- 把数据库TechInfo备份为'E:\Bak\db.bak'
    

           同样地,数据库的恢复也通过执行Transcat SQL的RESTORE DATABASE语句来进行数据库备份。RESTORE DATABASE的语法如下:

    --To restore a complete database from a full database backup (a Complete Restore):
    RESTORE DATABASE { database_name | @database_name_var } 
    [ FROM <backup_device> [ ,...n ] ]
    [ WITH 
       [ { CHECKSUM | NO_CHECKSUM } ]
       [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
       [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ] 
       [ [ , ] KEEP_REPLICATION ] 
       [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
       [ [ , ] MEDIAPASSWORD = { mediapassword |
                        @mediapassword_variable } ] 
       [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ] 
                    [ ,...n ] 
       [ [ , ] PASSWORD = { password | @password_variable } ] 
        [ [ , ] BLOCKSIZE = { blocksize | @blocksize_variable } ] 
        [ [ , ] BUFFERCOUNT = { buffercount | @buffercount_variable } ] 
       [ [ , ]    MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } ] 
       [ [ , ] ENABLE_BROKER ] 
       [ [ , ] ERROR_BROKER_CONVERSATIONS ] 
       [ [ , ] NEW_BROKER ] 
       [ [ , ] { RECOVERY | NORECOVERY | STANDBY = 
              {standby_file_name | @standby_file_name_var } 
       } ] 
       [ [ , ] REPLACE ] 
       [ [ , ] RESTART ] 
       [ [ , ] RESTRICTED_USER ] 
       [ [ , ] { REWIND | NOREWIND } ] 
       [ [ , ] { UNLOAD | NOUNLOAD } ] 
       [ [ , ] STATS [ = percentage ] ] 
        [ [ , ] { STOPAT = { 'date_time' | @date_time_var } 
        |  STOPATMARK = { 'lsn:lsn_number' }
                  [ AFTER 'datetime' ] 
        |  STOPBEFOREMARK = { 'lsn:lsn_number' }
                 [ AFTER 'datetime' ]
       } ] 
    ]
    [;]
    

    一个简单的例子是:

    RESTORE DATABASE  TechInfo  DISK = 'E:\Bak\db.bak'
    -- 从'E:\Bak\db.bak'恢复数据库TechInfo
    

    2.     记录表备份和恢复

            SQL Server单个表的备份略显复杂。

            第一步通过执行Transcat SQL的EXEC xp_cmdshell语句。EXEC xp_cmdshell的语法如下:

    EXEC xp_cmdshell 'bcp \"db_name..table_name\" out  bak_filep_path -c –Sserver_name -U -P%s'

    一个例子是:

    EXEC xp_cmdshell 'bcp \"TechInfo..student\" out  E:\\student.dmp -c –S\\192.168.1.1 –Uadmin  -P123456'

     第二步,调用SQL Server的组件SQLDMO.dll来动态生成一个建表sql脚本文件,C++代码大致如下:

    _SQLServerPtr  l_Server;
    l_Server.CreateInstance("SQLDMO.SQLServer");
    /*生成创建表的脚本文件*/
    		l_Server->Databases->Item((_bstr_t)DbName)->Tables->Item((_bstr_t)TableName)->Script(
    			SQLDMOScript_Default,
    			(_bstr_t)SqlScrptPath,    // 脚本文件的存贮路径
    			(_bstr_t)TableName,    // 给所备份的表起的新名字,这里依然沿用旧名字
    			SQLDMOScript2_Default);
    		l_Server->DisConnect();   // 断开连接
    

              很显然,对单个表的恢复需要用到建表的sql脚本文件和备份的数据文件,具体步骤也分为两步:

    a.    执行EXEC xp_cmdshell语句来运行sql脚本文件建表。EXEC xp_cmdshell执行sql脚本的语法如下:

    EXEC xp_cmdshell 'osql -U user_name -P password -S server_name -i sql_script_path -d db_name'

    一个简单的例子是:
    EXEC xp_cmdshell 'osql -U admin -P 123456 -S \\192.168.1.1 -i E:\\bak.sql -d TechInfo'

    b.    执行EXEC xp_cmdshell语句来为新建的表导入数据。EXECxp_cmdshell导入表数据的语法如下:

    EXEC xp_cmdshell 'bcp \"db_name..table_name\" in bak_file_path -c –Sserver_name –Uuser_name -Ppassword'
    一个简单的例子是:
    EXEC xp_cmdshell 'bcp \"TechInfo..student\" in E:\\db_bak.dmp -c –S\\192.168.1.1 –Uadmin –P123456'









  • 相关阅读:
    AngularJS7那些不得不说的事故
    Python和C++的混合编程(使用Boost编写Python的扩展包)
    为OPENCV添加freetype支持并显示中文字符(在mac上编译opencv及contrib库)
    OpenProject基础使用介绍
    负载均衡
    如何搭建wordpress ,wecenter
    nginx 模块
    Nginx
    http 协议
    ssh
  • 原文地址:https://www.cnblogs.com/lanzhi/p/6470561.html
Copyright © 2020-2023  润新知