{"id":171,"date":"2011-11-29T10:19:03","date_gmt":"2011-11-29T02:19:03","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=171"},"modified":"2013-12-30T14:18:03","modified_gmt":"2013-12-30T06:18:03","slug":"%e5%88%86%e7%a6%bb%e5%92%8c%e9%99%84%e5%8a%a0%e6%95%b0%e6%8d%ae%e5%ba%93%e6%96%87%e4%bb%b6","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=171","title":{"rendered":"\u5206\u79bb\u548c\u9644\u52a0\u6570\u636e\u5e93\u6587\u4ef6"},"content":{"rendered":"

\u5728\u5206\u79bb\u548c\u9644\u52a0\u6570\u636e\u5e93\u65f6\u53ef\u4ee5\u4f7f\u7528<\/span>sp_detach_db,<\/span>sp_attach_db\u548csp_attach_single_file_db\u7cfb\u7edf\u5b58\u50a8\u8fc7\u7a0b<\/span>,<\/span>\u8fd9\u4e09\u4e2a\u5b58\u50a8\u8fc7\u7a0b<\/span>\u5bf9\u4e8eSQL<\/span>\u00a0<\/span>Server\u6570\u636e\u5e93\u7ba1\u7406\u5458\u6267\u884c<\/span>\u4ee5\u4e0b<\/span>\u7684\u4efb\u52a1\u662f\u975e\u5e38\u65b9\u4fbf\u7684<\/span>:<\/span><\/span><\/span><\/p>\n

1.<\/span>\u4f7f\u7528sp_detach_db\u5c06\u6570\u636e\u5e93\u4ece\u4e00\u4e2a\u670d\u52a1\u5668\u5206\u79bb;<\/span><\/span><\/span><\/p>\n

2.<\/span>\u4f7f\u7528sp_attach_db\u7cfb\u7edf\u5b58\u50a8\u8fc7\u7a0b\u76f4\u63a5\u5c06.<\/span>mdf<\/span>\u548c.<\/span>ldf<\/span>\u6587\u4ef6\u9644\u52a0\u5230<\/span>\u6570\u636e\u5e93<\/span>\u670d\u52a1\u5668<\/span>;<\/span><\/span><\/span><\/p>\n

3.<\/span>\u4f7f\u7528sp_attach_single_file_db\u7cfb\u7edf\u5b58\u50a8\u8fc7\u7a0b\u53ea\u9644\u52a0.<\/span>mdf<\/span>\u6587\u4ef6<\/span>;<\/span><\/span><\/span><\/p>\n

\u5c3d\u7ba1<\/span>\u5b83\u4eec<\/span>\u5bf9\u4e8eSQL Server\u6570\u636e\u5e93\u7ba1\u7406\u5458\u662f\u5f88\u6709\u7528\u7684<\/span>,<\/span>\u4f46\u662f\u5728\u4f7f\u7528\u8fd9\u4e24\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u65f6\u662f\u6709\u4e00\u4e9b\u9650\u5236\u7684<\/span>,<\/span>\u5982<\/span>:<\/span><\/span><\/span><\/p>\n

1<\/span>.<\/span>\u4e0d\u80fd\u9644\u52a0\u591a\u4e2a\u65e5\u5fd7\u6587\u4ef6<\/span>;<\/span><\/span><\/span><\/p>\n

2<\/span>.<\/span>\u4e0d\u80fd\u9644\u52a016\u4e2a\u4ee5\u4e0a\u7684\u6587\u4ef6<\/span>;<\/span><\/span><\/span><\/p>\n

\u5728SQL Server 2008\u4e2d<\/span>,<\/span>\u5fae\u8f6f\u5ba3\u5e03\u4e0a\u9762\u7684\u7cfb\u7edf\u5b58\u50a8\u8fc7\u7a0b\u5c06\u5728\u672a\u6765\u7684\u7248\u672c\u4e2d\u88ab\u5e9f\u5f03<\/span>.<\/span>\u800c\u5728<\/span>“CREATE DATABASE”<\/span>SQL\u8bed\u53e5\u4e2d\u6dfb\u52a0\u4e86\u4e00\u4e2a\u4ece\u53e5”<\/span>FOR ATTACH<\/span>“<\/span>.<\/span>\u5e76\u5efa\u8bae\u5e94\u8be5\u4f7f\u7528<\/span>“<\/span>CREATE DATABASE database_name FOR ATTACH<\/span>“<\/span>\u8bed\u53e5<\/span><\/span><\/span><\/p>\n

\u4e0b\u9762\u4ecb\u7ecd\u4f7f\u7528”<\/span>FOR ATTACH<\/span>“\u8bed\u53e5\u7684\u7528\u6cd5<\/span>,<\/span>\u4ee5\u514b\u670d\u5728\u4f7f\u7528sp_attach_db\u548csp_attach_single_file_db\u65f6\u8981\u9762\u4e34\u7684\u9650\u5236<\/span>.<\/span><\/span><\/span><\/p>\n

1.<\/span>\u521b\u5efa\u5b9e\u4f8b\u6570\u636e\u5e93<\/span><\/span><\/span><\/p>\n

\n

\n
\n
[c-sharp]<\/b>\u00a0view plain<\/a>copy<\/a><\/p>\n
<\/div>\n<\/div>\n<\/div>\n
    \n
  1. USE\u00a0master\u00a0\u00a0<\/span><\/span><\/li>\n
  2. GO\u00a0\u00a0<\/span><\/li>\n
  3. CREATE\u00a0DATABASE\u00a0Test\u00a0ON\u00a0\u00a0<\/span><\/li>\n
  4. (NAME\u00a0=\u00a0‘Test’<\/span>,\u00a0FILENAME\u00a0=\u00a0<\/span>‘D:\/Test.mdf’<\/span>,\u00a0SIZE\u00a0=\u00a03072KB\u00a0,\u00a0FILEGROWTH\u00a0=\u00a01024KB\u00a0)\u00a0\u00a0<\/span><\/span><\/li>\n
  5. LOG\u00a0ON\u00a0\u00a0<\/span><\/li>\n
  6. (NAME\u00a0=\u00a0‘Test_log’<\/span>,\u00a0FILENAME\u00a0=\u00a0<\/span>‘D:\/Test_log.ldf’<\/span>,\u00a0SIZE\u00a0=\u00a01024KB\u00a0,\u00a0FILEGROWTH\u00a0=\u00a010%)\u00a0\u00a0<\/span><\/span><\/li>\n
  7. GO\u00a0\u00a0<\/span><\/li>\n<\/ol>\n<\/div>\n

     <\/p>\n

    2.<\/span>\u4f7f\u7528sp_detach_db\u5206\u79bb\u8be5\u6570\u636e\u5e93<\/span>\u5e76\u4f7f\u7528<\/span>sp_attach_db\u5c06\u5b83\u91cd\u65b0\u9644\u52a0<\/span><\/span><\/span><\/p>\n

    \n

    \n
    \n
    [c-sharp]<\/b>\u00a0view plain<\/a>copy<\/a><\/p>\n
    <\/div>\n<\/div>\n<\/div>\n
      \n
    1. USE\u00a0master\u00a0\u00a0<\/span><\/span><\/li>\n
    2. GO\u00a0\u00a0<\/span><\/li>\n
    3. —\u00a0\u5206\u79bb\u6570\u636e\u5e93\u00a0\u00a0<\/span><\/li>\n
    4. EXEC\u00a0sp_detach_db\u00a0‘Test’<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
    5. GO\u00a0\u00a0<\/span><\/li>\n
    6. —\u00a0\u9644\u52a0\u6570\u636e\u5e93\u00a0\u00a0<\/span><\/li>\n
    7. EXEC\u00a0sp_attach_db\u00a0‘Test’<\/span>,\u00a0<\/span>‘D:\/Test.mdf’<\/span>,\u00a0<\/span>‘D:\/Test_log.ldf’<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
    8. GO\u00a0\u00a0<\/span><\/li>\n<\/ol>\n<\/div>\n

       <\/p>\n

      3.<\/span>\u4f7f\u7528<\/span>“CREATE DATABASE\u00a0<\/span>database_name<\/span>\u00a0FOR ATTACH”<\/span>\u8bed\u53e5\u9644\u52a0\u6570\u636e\u5e93<\/span><\/span><\/span><\/p>\n

      \n

      \n
      \n
      [c-sharp]<\/b>\u00a0view plain<\/a>copy<\/a><\/p>\n
      <\/div>\n<\/div>\n<\/div>\n
        \n
      1. USE\u00a0master\u00a0\u00a0<\/span><\/span><\/li>\n
      2. GO\u00a0\u00a0<\/span><\/li>\n
      3. —\u00a0\u5206\u79bb\u6570\u636e\u5e93\u00a0\u00a0<\/span><\/li>\n
      4. EXEC\u00a0sp_detach_db\u00a0‘Test’<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
      5. GO\u00a0\u00a0<\/span><\/li>\n
      6. —\u00a0\u4f7f\u7528“CREATE\u00a0DATABASE\u00a0database_name\u00a0FOR\u00a0ATTACH”<\/span>\u9644\u52a0\u6570\u636e\u5e93\u00a0\u00a0<\/span><\/span><\/li>\n
      7. CREATE\u00a0DATABASE\u00a0Test\u00a0ON\u00a0\u00a0<\/span><\/li>\n
      8. (FILENAME\u00a0=\u00a0‘D:\/Test.mdf’<\/span>),\u00a0\u00a0<\/span><\/span><\/li>\n
      9. (FILENAME\u00a0=\u00a0‘D:\/Test_log.ldf’<\/span>)\u00a0\u00a0<\/span><\/span><\/li>\n
      10. FOR\u00a0ATTACH\u00a0\u00a0<\/span><\/li>\n
      11. GO\u00a0\u00a0<\/span><\/li>\n<\/ol>\n<\/div>\n

         <\/p>\n

        4.<\/span>\u5206\u79bb\u6570\u636e\u5e93\u5e76\u5220\u9664\u65e5\u5fd7<\/span>(.ldf)<\/span>\u6587\u4ef6<\/span><\/span><\/span><\/p>\n

        \n

        \n
        \n
        [c-sharp]<\/b>\u00a0view plain<\/a>copy<\/a><\/p>\n
        <\/div>\n<\/div>\n<\/div>\n
          \n
        1. USE\u00a0master\u00a0\u00a0<\/span><\/span><\/li>\n
        2. GO\u00a0\u00a0<\/span><\/li>\n
        3. —\u00a0\u5206\u79bb\u6570\u636e\u5e93\u00a0\u00a0<\/span><\/li>\n
        4. EXEC\u00a0sp_detach_db\u00a0‘Test’<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
        5. GO\u00a0\u00a0<\/span><\/li>\n
        6. —\u00a0\u5220\u9664\u65e5\u5fd7\u6587\u4ef6\u00a0\u00a0<\/span><\/li>\n
        7. EXEC\u00a0master..xp_cmdshell\u00a0‘del\u00a0“D:\/Test_log.ldf”‘<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
        8. GO\u00a0\u00a0<\/span><\/li>\n
        9. —\u00a0\u5982\u679ccmdshell\u529f\u80fd\u4e0d\u53ef\u4f7f\u7528\u5219\u4f7f\u7528\u4e0b\u9762\u8bed\u53e5\u6fc0\u6d3bcmdshell\u529f\u80fd\u00a0\u00a0<\/span><\/li>\n
        10. USE\u00a0master\u00a0\u00a0<\/span><\/li>\n
        11. GO\u00a0\u00a0<\/span><\/li>\n
        12. sp_configure\u00a0‘show\u00a0advanced\u00a0options’<\/span>,\u00a01\u00a0\u00a0<\/span><\/span><\/li>\n
        13. GO\u00a0\u00a0<\/span><\/li>\n
        14. RECONFIGURE\u00a0WITH\u00a0OVERRIDE\u00a0\u00a0<\/span><\/li>\n
        15. GO\u00a0\u00a0<\/span><\/li>\n
        16. SP_CONFIGURE\u00a0‘xp_cmdshell’<\/span>,\u00a01\u00a0\u00a0<\/span><\/span><\/li>\n
        17. RECONFIGURE\u00a0WITH\u00a0OVERRIDE\u00a0\u00a0<\/span><\/li>\n
        18. GO\u00a0\u00a0<\/span><\/li>\n<\/ol>\n<\/div>\n

           <\/p>\n

          5.<\/span>\u4f7f\u7528sp_attach_single_file_db\u9644\u52a0.<\/span>mdf<\/span>\u6587\u4ef6<\/span><\/span><\/span><\/p>\n

          \n

          \n
          \n
          [c-sharp]<\/b>\u00a0view plain<\/a>copy<\/a><\/p>\n
          <\/div>\n<\/div>\n<\/div>\n
            \n
          1. USE\u00a0master\u00a0\u00a0<\/span><\/span><\/li>\n
          2. GO\u00a0\u00a0<\/span><\/li>\n
          3. EXEC\u00a0sp_attach_single_file_db\u00a0‘Test’<\/span>,\u00a0<\/span>‘D:\/Test.mdf’<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
          4. GO\u00a0\u00a0<\/span><\/li>\n<\/ol>\n<\/div>\n

             <\/p>\n

            6.<\/span>\u5220\u9664\u65e5\u5fd7\u6587\u4ef6<\/span>,<\/span>\u4f7f\u7528<\/span>“CREATE DATABASE\u00a0<\/span>database_name<\/span>\u00a0FOR\u00a0<\/span>ATTACH_REBUILD_LOG<\/span>“<\/span>\u9644\u52a0\u6570\u636e\u5e93<\/span><\/span><\/span><\/p>\n

            \n

            \n
            \n
            [c-sharp]<\/b>\u00a0view plain<\/a>copy<\/a><\/p>\n
            <\/div>\n<\/div>\n<\/div>\n
              \n
            1. USE\u00a0master\u00a0\u00a0<\/span><\/span><\/li>\n
            2. GO\u00a0\u00a0<\/span><\/li>\n
            3. EXEC\u00a0sp_detach_db\u00a0‘Test’<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
            4. GO\u00a0\u00a0<\/span><\/li>\n
            5. —\u00a0\u5220\u9664\u65e5\u5fd7\u6587\u4ef6\u00a0\u00a0<\/span><\/li>\n
            6. EXEC\u00a0master..xp_cmdshell\u00a0‘del\u00a0“D:\/Test_log.ldf”‘<\/span>\u00a0\u00a0<\/span><\/span><\/li>\n
            7. GO\u00a0\u00a0<\/span><\/li>\n
            8. —\u00a0\u4f7f\u7528“CREATE\u00a0DATABASE\u00a0database_name\u00a0FOR\u00a0ATTACH_REBUILD_LOG”<\/span>\u9644\u52a0\u6570\u636e\u5e93\u00a0\u00a0<\/span><\/span><\/li>\n
            9. CREATE\u00a0DATABASE\u00a0Test\u00a0ON\u00a0\u00a0<\/span><\/li>\n
            10. (FILENAME\u00a0=\u00a0‘D:\/Test.mdf’<\/span>)\u00a0\u00a0<\/span><\/span><\/li>\n
            11. FOR\u00a0ATTACH_REBUILD_LOG\u00a0\u00a0<\/span><\/li>\n
            12. GO \u00a0<\/span><\/li>\n<\/ol>\n<\/div>\n

               <\/p>\n","protected":false},"excerpt":{"rendered":"

              \u5728\u5206\u79bb\u548c\u9644\u52a0\u6570\u636e\u5e93\u65f6\u53ef\u4ee5\u4f7f\u7528sp_detach_db,sp_attach_db\u548csp_attach_singl […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[16],"class_list":["post-171","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql_server_2008_r2"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/171","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=171"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/171\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=171"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}