×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Florida

Copying Data Between Servers

zxcvb(闷死了！)
本文发表在 rolia.net 枫下论坛To bulk copy data from one Microsoft® SQL Server™ database to another, data from the source database must first be bulk copied into a file. The file is then bulk copied into the destination database.

After bulk copying data into a table, if the recovery model is simple, then a full or differential backup is recommended. If the recovery model is bulk-logged or full, a log backup is sufficient.



Note Native, character, and Unicode format bcp can be used to bulk copy data between different instances of SQL Server on different processor architectures. However, the same format must be used when importing as exporting.


Storing information in Unicode native format is useful when information must be copied from one instance of SQL Server to another. Using native format for noncharacter data saves time, preventing unnecessary conversion of data types to and from character format. Using Unicode character format for all character data prevents loss of any extended characters when bulk loading data between servers using different code pages (character loss is possible if extended characters are copied into non-Unicode columns and the extended character cannot be represented). However, a data file in Unicode native format cannot be read by any program other than bcp or the BULK INSERT statement.

It is also possible to copy data from one SQL Server database to another using:

The DTS Import/Export Wizard.


The Transact-SQL statements BACKUP and RESTORE (to copy entire databases).


Distributed queries as part of an INSERT statement.


The SELECT INTO statement.更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#1135458@0)
Last Updated: 2003-4-11
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / please help: I want to use BCP to export/import a big table, I know a sample bcp command, but where I type it, an article said type as inside: -shinning(dudu); 2003-4-11 {208} (#1135156@0)
    bcp "SELECT * FROM pubs..authors" queryout authors.txt -U garth -P pw -c

    To execute the statement, go to the Command Prompt, what is Command Prompt.........:-( ji, ji ,ji .ji,...........................
    • I'm using SQL Server 2000..... -shinning(dudu); 2003-4-11 (#1135159@0)
    • up.....I only can use BCP to do it, please tell me if anybody knows..can I write it in query analyzer or stored procedures.thanks a lot -shinning(dudu); 2003-4-11 (#1135305@0)
      • from START-->RUN, type your commond -zxcvb(闷死了！); 2003-4-11 (#1135362@0)
        • thank you soooooooooooooooooooo much. ~~.~~ and I also want to import this file to a remote sql server DB, is it same way, -->start-->run the commands??????? -shinning(dudu); 2003-4-11 (#1135391@0)
          • from sql server enterprise manager, right click on the table you want to export, choose export -zxcvb(闷死了！); 2003-4-11 (#1135401@0)
          • Copying Data Between Servers -zxcvb(闷死了！); 2003-4-11 {1614} (#1135458@0)
            本文发表在 rolia.net 枫下论坛To bulk copy data from one Microsoft® SQL Server™ database to another, data from the source database must first be bulk copied into a file. The file is then bulk copied into the destination database.

            After bulk copying data into a table, if the recovery model is simple, then a full or differential backup is recommended. If the recovery model is bulk-logged or full, a log backup is sufficient.



            Note Native, character, and Unicode format bcp can be used to bulk copy data between different instances of SQL Server on different processor architectures. However, the same format must be used when importing as exporting.


            Storing information in Unicode native format is useful when information must be copied from one instance of SQL Server to another. Using native format for noncharacter data saves time, preventing unnecessary conversion of data types to and from character format. Using Unicode character format for all character data prevents loss of any extended characters when bulk loading data between servers using different code pages (character loss is possible if extended characters are copied into non-Unicode columns and the extended character cannot be represented). However, a data file in Unicode native format cannot be read by any program other than bcp or the BULK INSERT statement.

            It is also possible to copy data from one SQL Server database to another using:

            The DTS Import/Export Wizard.


            The Transact-SQL statements BACKUP and RESTORE (to copy entire databases).


            Distributed queries as part of an INSERT statement.


            The SELECT INTO statement.更多精彩文章及讨论，请光临枫下论坛 rolia.net
      • why don't u use dsn file? it's easy than bulk copy -zxcvb(闷死了！); 2003-4-11 (#1135379@0)
        • because it have 45000 records, and size of log file is limited, it failed by using DTS, and web admin told me must use BCP... -shinning(dudu); 2003-4-11 (#1135420@0)
      • Getting Started with Command Prompt Utilities -zxcvb(闷死了！); 2003-4-11 {2281} (#1135388@0)
        本文发表在 rolia.net 枫下论坛The command prompt utilities are installed automatically when you install the Microsoft® SQL Server™ 2000 utilities on a computer running Microsoft Windows® 2000, Microsoft Windows NT®, Microsoft Windows 95, or Microsoft Windows 98. The table shows the utilities and the directories where they are installed.

        Directory Utilities
        x:\Program Files\Microsoft SQL Server\MSSQL\Binn1 bcp2
        console
        isql2
        sqlagent
        sqldiag
        sqlmaint
        sqlservr
        vswitch
        x:\Program Files\Microsoft SQL Server\80\Tools\Binn bcp2
        dtsrun
        dtswiz
        isql2
        isqlw
        itwiz
        odbccmpt
        osql
        rebuildm
        sqlftwiz
        x:\Program Files\Microsoft SQL Server\80\Com distrib3
        logread3
        replmerg3
        snapshot3
        x:\Program Files\Common Files\Microsoft Shared\Service Manager scm
        x:\Program Files\Common Files\Microsoft Shared\??? regxmlss


        1 MSSQL is the directory name for the default instance of SQL Server 2000. For each named instance of SQL Server 2000, the corresponding directory name is MSSQL$instance_name.
        2 The bcp and isql utilities are installed in both the \MSSQL\Binn directory and the \80\Tools\Binn directory. They are also installed in the \MSSQL$instance_name\Binn directory for each named instance of SQL Server 2000. Any copy of these utilities may be used to connect to any instance of SQL Server. The instance of SQL Server to connect to is determined for each utility by a server argument. You are not limited to the instance of SQL Server corresponding to the directory in which the utility is run.
        3 These are the file names for the four replication agent utilities.

        During installation, the x:\Program Files\Microsoft SQL Server\80\Tools\Binn directory is added to the system path. You can run the utilities in this directory at any command prompt. For a utility not in the 80\Tools\Binn directory, you must either run the utility from a command prompt in the directory in which it is installed or explicitly specify the path.

        These utilities are no longer installed by SQL Server 2000 Setup.

        makepipe utility


        odbcping utility


        readpipe utility
        If you need to run these utilities, you can run them from the x:\x86\Binn directory on the SQL Server 2000 compact disc, or manually copy them to your computer.更多精彩文章及讨论，请光临枫下论坛 rolia.net
        • I found the bcp.exe, I double click it, but it can not run, it show so quickly, even I can not see it..:-( -shinning(dudu); 2003-4-11 (#1135439@0)
    • a........................how do i do, I try to use BCP, but failed lots of times, and even I can not see error massage, because the windows closed so quickly.....:( -shinning(dudu); 2003-4-11 (#1135490@0)
      • try this: bcp pubs..authors out authors.txt -c -zxcvb(闷死了！); 2003-4-11 {106} (#1135714@0)
        a pop window will will ask password, type ur pwd in and hit enter. the text file will be created under c:/
    • another total windows guy, only knows window, hehe. BCP is a command tool, means you have to open a console (e.g dos prompt) then type the command with correct parameters. -dibert(呆伯); 2003-4-11 (#1135931@0)
      • BTW, 45000 is not a big number, I've ever bcped 4 million records, (but in Sybase not SQL server). no problem for bcp out, but when u bcp in, something should be paid attention: -dibert(呆伯); 2003-4-11 {211} (#1135956@0)
        these are for Sybase, but may SQL Server also.
        1) fast or slow bcp (for log increasement)
        2) -b option (for tran size)
        3) -E option (for identity column)
        If bcp for SQL server don't have above, forget them.

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论