{"id":4371,"date":"2018-03-17T09:46:01","date_gmt":"2018-03-17T01:46:01","guid":{"rendered":"https:\/\/blog.hoyo.idv.tw\/?p=4371"},"modified":"2023-04-22T08:51:37","modified_gmt":"2023-04-22T00:51:37","slug":"ms-sql-server-%e6%af%8f%e6%97%a5%e5%ae%9a%e6%99%82%e5%82%99%e4%bb%bd%e3%80%81%e9%82%84%e5%8e%9f","status":"publish","type":"post","link":"https:\/\/blog.hoyo.idv.tw\/?p=4371","title":{"rendered":"Microsoft SQL Server 2000 \u6bcf\u65e5\u5b9a\u6642\u5099\u4efd\u3001\u9084\u539f"},"content":{"rendered":"

(\u820a\u8cc7\u6599\u56de\u7210\u7cfb\u5217<\/strong>)<\/p>\n

\u5982\u679c\u53ea\u662f\u55ae\u7d14\u7684\u5099\u4efd\uff0c\u90a3 MS SQL Server \u7684 mmc \u5de5\u5177 Enterprise Manager \u5c31\u53ef\u4ee5\u7c21\u55ae\u7684\u9054\u6210\u4efb\u52d9\u3002
\n\u4e0d\u904e\u8981\u662f\u9700\u8981\u5c07\u8cc7\u6599\u5eab\u5099\u4efd\u5f8c\u9084\u539f\u5230\u5099\u63f4\u4e3b\u6a5f\u4e0a\uff0c\u90a3\u6b65\u9a5f\u5c31\u6703\u591a\u4e00\u9ede\uff0c\u9084\u597d\u5176\u5be6\u4e5f\u6c92\u8907\u96dc\u5230\u54ea\u88e1\u53bb\uff0c\u53ea\u662f\u9700\u8981\u4f7f\u7528\u52d5\u52d5\u4f60\u7684\u624b\u6307\u982d\u6253\u6253\u5e7e\u500b\u6307\u4ee4\u3002<\/p>\n

\u6d41\u7a0b<\/p>\n

    \n
  1. \u5c07\u8cc7\u6599\u5eab\u5099\u4efd<\/li>\n
  2. \u5099\u4efd\u6a94\u6848\u8907\u88fd\u5230\u9084\u539f\u4e3b\u6a5f\u4e0a<\/li>\n
  3. \u5c07\u5099\u4efd\u8cc7\u6599\u5eab\u9084\u539f<\/li>\n<\/ol>\n

    --<\/p>\n

    \u5be6\u4f5c<\/h2>\n

    \u74b0\u5883\u8aaa\u660e\uff1a\u6240\u6709\u52d5\u4f5c\u7686\u5728\u5099\u4efd\u7aef (\u4e5f\u5c31\u662f\u8ca0\u8cac\u5099\u4efd\u7684\u96fb\u8166) \u5b8c\u6210<\/p>\n

    1. \u5efa\u7acb backup.sql
    \n\u5099\u4efd\u8cc7\u6599\u5eab\u7684 T-SQL \u6307\u4ee4 c:\\backup\\backup.sql<\/p>\n

    BACKUP DATABASE database_name\r\nTO DISK='e:\\backup\\hhdb.bak'\r\nWITH FORMAT\r\nGO<\/pre>\n
    \u5229\u7528\u6b64 sql \u6307\u4ee4\u5c07\u6307\u5b9a\u7684\u8cc7\u6599\u5eab\u5099\u5206\u5230 TO DISK \u6307\u5b9a\u7684\u8def\u5f91\u3001\u6a94\u540d<\/p>\n
    2. \u5efa\u7acb restore.sql
    \n\u9084\u539f\u8cc7\u6599\u5eab\u7684 T-SQL \u6307\u4ee4 c:\\backup\\restore.sql<\/p>\n
    USE master\r\nGO\r\nALTER DATABASE hhdb SET single_user WITH rollback immediate\r\nGO\r\nRESTORE DATABASE hhdb FROM \r\nDISK='c:\\sql\\hhdb.bak' WITH RECOVERY\r\nGO\r\nALTER DATABASE db_name SET multi_user\r\nGO<\/pre>\n
    \u9019\u88e1\u7684\u52d5\u4f5c\u5c31\u662f<\/p>\n
      \n
    1. \u4f7f\u7528 ALTER DATABASE hhdb SET single_user WITH rollback immediate \u5c07\u9700\u8981\u9084\u539f\u7684\u8cc7\u6599\u5eab\u8a2d\u5b9a\u6210\u300c\u55ae\u4eba\u6a21\u5f0f\u300d<\/li>\n
    2. \u9084\u539f\u8cc7\u6599\u5eab<\/li>\n
    3. \u57f7\u884c ALTER DATABASE db_name SET multi_user \u6062\u5fa9\u8cc7\u6599\u5eab\u6b63\u5e38\u904b\u4f5c<\/li>\n<\/ol>\n
      3. \u5efa\u7acb backup.bat
      \n\u8ca0\u8cac\u5b9a\u6642\u6392\u6210\u57f7\u884c\u7684\u6279\u6b21\u6a94 c:\\backup\\backup.bat<\/p>\n
      osql -S \"dbserver\" -U sa -P \"password\" -i c:\\backup\\backup.sql\r\ncopy \\\\192.168.1.2\\E$\\backup\\hhdb.bak c:\\sql\\hhdb.bak\r\nosql -S \"192.168.1.4\" -U sa -P \"password\" -i c:\\backup\\restore.sql\r\nexit<\/pre>\n
      \u6392\u6210\u8a2d\u5b9a<\/p>\n
      \u5b8c\u6210\u5efa\u7acb\u7684\u4e09\u500b\u6a94\u6848<\/p>\n
      \u4e0b\u4e00\u6b65<\/p>\n
      \u9ede\u9078\u300c\u700f\u89bd\u300d<\/p>\n
      \u9078\u64c7\u525b\u525b\u5efa\u7acb\u7684\u6279\u6b21\u6a94 backup.bat<\/p>\n
      \u9078\u64c7\u5b9a\u6642\u6392\u7a0b\u57f7\u884c\u6642\u9593<\/p>\n
      \u6b64\u7bc4\u4f8b\u70ba\u9078\u64c7\u300c\u6bcf\u65e5\u300d\u5f8c\u7684\u9078\u9805<\/p>\n
      \u8a2d\u5b9a\u6392\u7a0b\u57f7\u884c\u6642\u7684\u5e33\u865f\u4ee5\u53ca\u5bc6\u78bc<\/p>\n
      --<\/p>\n
      \u53c3\u8003<\/h2>\n
      osql \u6307\u4ee4\u53c3\u6578<\/p>\n
      osql [-U login id] [-P password] \r\n[-S server] [-H hostname] [-E trusted connection] \r\n[-d use database name] [-l login timeout] [-t query timeout] \r\n[-h headers] [-s colseparator] [-w columnwidth] \r\n[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] \r\n[-L list servers] [-c cmdend] [-D ODBC DSN name] \r\n[-q \"cmdline query\"] [-Q \"cmdline query\" and exit] \r\n[-n remove numbering] [-m errorlevel] \r\n[-r msgs to stderr] [-V severitylevel] \r\n[-i inputfile] [-o outputfile] \r\n[-p print statistics] [-b On error batch abort] \r\n[-O use Old ISQL behavior disables the following] \r\n<EOF> batch processing \r\nAuto console width scaling \r\nWide messages \r\ndefault errorlevel is -1 vs 1 \r\n[-? show syntax summary]<\/pre>\n
      --<\/p>\n
      <\/div>\n
      <\/path><\/svg><\/i>  4,552 total views<\/p>\n
      <\/div>\n","protected":false},"excerpt":{"rendered":"
      (\u820a\u8cc7\u6599\u56de\u7210\u7cfb\u5217) \u5982\u679c\u53ea\u662f\u55ae...<\/p>\n
      <\/div>\n
      <\/path><\/svg><\/i>  4,552 total views<\/p>\n
      <\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[263,337],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/4371"}],"collection":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4371"}],"version-history":[{"count":5,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/4371\/revisions"}],"predecessor-version":[{"id":4376,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/4371\/revisions\/4376"}],"wp:attachment":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4371"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4371"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4371"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}