Six methods of data Movement for SQL Server 7.0 databases

server| Data | Six kinds of data movement methods for database SQL Server 7.0 databases from: Century Easy Web March 3, 2003 20:6 I am working as a database administrator to maintain databases in multiple servers,  Often a server in a database to move to another server, the movement of data some experience, I hope to communicate with you.


1. Import or export through the Designer for tool DTS


DTS Designer features a powerful, multitasking, visual interface, easy to operate, but not many people know, if only for the SQL Server database to move some of the tables, this method is best, of course, you can also carry out all the table movement. In SQL Server Enterprise Manager, expand the + on the left side of the server, select the database, right-click, select All Tasks/import Data ... (or all tasks/export data ...), enter the wizard mode, follow the prompts step-by-step walk on the line, the inside is very fine, you can flexibly copy data between different sources, very convenient. And can be saved as a DTS package, if you have the same replication tasks, directly run DTS package on the line, save time and effort. You can also open DTS Designer directly by expanding the data transformation Services under the server name, selecting Local Packages, right-clicking in the right window, selecting new Package, and opening DTS Designer.  It is worth noting that if the source database is to be copied from a table with a foreign key, pay attention to the order of movement, sometimes to move in batches, or foreign key primary key, index may be lost, move the option next to the hint said very clearly, or one-time copy to the target database, and then re-establish foreign keys, primary keys, index.


in fact, the establishment of the database, the establishment of foreign keys, primary key, the index of the file should be separated from the table file, and the use of data files are also separated, and placed on different drives, to facilitate the optimization of the database.


2. Using the bcp tool


Although this tool is not recommended in the SQL Server7 version, many database administrators still like to use it, especially those who have used earlier versions of SQL Server. BCP has limitations, first its interface is not graphical, second, it is only in the SQL Server table (view) and text file replication between, but it has the advantage of good performance, low overhead, less memory, faster.  Interested friends can look up the reference manual.


3. Leverage backup and Recovery


a full backup of the source database, backed up to a device (device), and then copies the backup file to the destination server (Fast recovery), restores the database, and fills in the name of the source database in the restored database name (the name must be the same). Select Forced recovery (you can overwrite the previous database options), select Restore from Device, and select backed up files when browsing.  This method can completely restore the database, including foreign keys, primary keys, and indexes.


4. Direct copy data File


copies the data files (*.mdf) and log files (*.ldf) of the database to the destination server and restores the statements in SQL Server Query Analyzer:


EXEC sp_attach_db @dbname = ' Test ',


@filename1 = ' d:\mssql7\data\test_data.mdf ',


@filename2 = ' d:\mssql7\data\test_log.ldf '


This allows you to attach the test database to SQL Server and use it as usual. If you do not want to use the original log file, you can use the following command:


EXEC sp_detach_db @dbname = ' Test '


EXEC sp_attach_single_file_db @dbname = ' Test ',


@physname = ' d:\mssql7\data\test_data.mdf '


The purpose of this statement is simply to load the data file, which can be automatically added by the SQL Server database, but the data recorded in the original log file is lost.


5. Customizing
in the Application

can execute its own program in the application (PB, VB), also can execute in Query Analyzer, this method is more flexible, actually use a platform to connect to the database, in the platform uses the main time SQL statement, this method has little influence to the database, However, if you use a remote linked server, the transmission between the network requires good performance, there are generally two kinds of statements:


1> Select ... into new_tablename where ...


2> Insert (INTO) old_tablename Select ... from ... where ...

The
difference is that the former inserts data into a new table (creating a table and then inserting data), which inserts data into a table that already exists, and I personally like the latter, because the second statement is stronger than the former in terms of the programmatic structure of the application.


6. SQL Server replication Features


SQL Server provides a powerful data replication capabilities, but also the most difficult to grasp, the specific application please refer to the relevant information, it is noteworthy that in order to successfully replicate the data, some conditions are essential:

The
1>sql Server agent must be started and MSDTC must start.


2> all tables to be replicated must have a primary key.


3> If there is a text or image data type in the table, you must use the WITH LOG option and you cannot use the WITH NO_LOG option.


another max text repl size option controls the maximum size of text and image data that can be copied, and operations that exceed this limit will fail.


4> on the computer to be replicated, it should be at least implicitly shared, that is, the share name is C $ or d$ ....


5> The Windows NT account that is used for SQL Server Agent cannot be a local system account because the local System account does not allow network access.


6> If the server participating in the replication is in another computer domain, a trust relationship must be established between these domains.








(edit: Weimin