Copying Databases to Other Servers
Creating database backups allows you to copy data from one computer to another. The copied database can be used for testing, checking consistency, developing software, running reports, or possibly making databases available to remote branch operations. By copying a database from one computer to another, it is possible to reduce resource contention because processing is offloaded to other computers. Copied databases restored onto separate computers are often used for read-only operations.
Note With Microsoft® SQL Server™ 2000, the sort order and code page of the database being copied is no longer a concern. SQL Server now handles multiple collations.
A database can also be copied to another computer to act as a standby server. The database and the transaction logs are copied to another computer periodically, which can be brought online if the primary computer fails for some reason. The level of synchronization between the primary computer and the standby server is determined by how often regular backups of the primary computer are created and then applied to the standby server. For more information, see Using Standby Servers.
Note It is possible to back up and restore databases between computers running an instance of SQL Server on Microsoft Windows NT® 4.0, Microsoft Windows® 2000, and Windows 98.
Other methods for copying data between multiple instances of SQL Server include using:
- The Data Transformation Services (DTS) Import/Export Wizard to copy and modify data between any ODBC, OLE DB, or text data source and an instance of SQL Server.
- The bcp utility to copy data between an instance of SQL Server and a data file, using native, character, or Unicode mode.
- The INSERT statement, which uses a distributed query as the select list to extract data from another data source.
- The Copy Database Wizard to copy or move databases and associated meta data between servers.