Shulou(Shulou.com)05/31 Report--

This article mainly talks about "what are the mobile methods of SQLServer database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the mobile methods of SQLServer database"?

What are the mobile methods of SQLServer database

1. Through the tool DTS designer to import or export DTS designer powerful, support multitasking, but also a visual interface, easy to operate, but generally not many people know, if only part of the table in the SQLServer database to move, it is best to use this method, of course, you can also move all tables. In SQLServerEnterpriseManager, expand the + on the left side of the server, select the database, right-click, and select Alltasks/ImportData... (or Alltasks/ExportData...), enter the wizard mode and follow the instructions step by step, which is divided into very fine, flexible replication of data between different data sources, which is very convenient. And you can save it as a DTS package. If you have the same copy task in the future, just run the DTS package directly to save time and effort. You can also open the DTS designer directly by expanding DataTransformationServices under the server name, selecting LocalPackages, right-clicking in the right window, and selecting NewPackage, which opens the DTS designer. It is worth noting that: if the table to be copied in the source database has a foreign key, pay attention to the order of movement, and sometimes move in batches, otherwise the primary key of the foreign key and the index may be lost, and the prompt next to the option is very clear when you move. or copy to the target database at one time, and then re-establish the foreign key, primary key, index.

In fact, when establishing a database, the files of foreign keys, primary keys and indexes should be separated from the table files, and the data files used should be separated and placed on different drives, which is conducive to the optimization of the database.

two。 Using Bcp tools

Although this tool is not recommended in versions of SQLServer7, many database administrators still like to use it, especially those who have used earlier versions of SQLServer. Bcp has limitations, first of all, its interface is not graphical, and secondly, it only copies between SQLServer tables (views) and text files, but its advantages are good performance, low overhead, low memory consumption and high speed. Interested friends can look up the reference manual.

3. Take advantage of backup and recovery

First, make a full backup of the source database, backup to a device (device), and then copy the backup files to the destination server (fast recovery), perform the database recovery operation, fill in the name of the source database in the name of the restored database (the name must be the same), select the forced restore (an option that can overwrite the previous database), and then choose to restore from the device. Just select the backed up file when browsing. This method can fully recover the database, including foreign keys, primary keys, and indexes.

What are the mobile methods of SQLServer database

4. Copy data files directly

Copy the data files (* .mdf) and log files (* .ldf) of the database to the destination server, and restore them with statements in SQLServerQueryAnalyzer:

EXECsp_attach_db@dbname='test'

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

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

This attaches the test database to the SQLServer and can be used as usual. If you do not want to use the original log file, you can use the following command:

EXECsp_detach_db@dbname='test'

EXECsp_attach_single_file_db@dbname='test'

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

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

5. Customize in the application

You can execute the program written by yourself in the application program (PB, VB) or in QueryAnalyzer. This method is more flexible. In fact, it uses a platform to connect to the database, and the SQL statement is mainly used in the platform. This method has little impact on the database, but if you use a remote linked server, it requires good transmission performance between networks. Generally, there are two kinds of statements:

1 > select...intonew_tablenamewhere...

2 > insert (into) old_tablenameselect...from...where...

The difference is that the former inserts the data into a new table (first creating the table, then inserting the data), while the latter inserts the data into an existing table. I personally like the latter, because in terms of programming structure and scope of application, the second statement is stronger than the former.

Replication function of 6.SQLServer

SQLServer provides a powerful data replication feature, which is also the most difficult to master. For specific applications, please refer to the relevant materials. It is worth noting that some conditions are essential for successful data replication:

1 > SQLServerAgent must be started and MSDTC must be started.

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

3 > if there is a text or image data type in the table, you must use the withlog option, not the withno_log option.

In addition, the maxtextreplsize option controls the maximum size of text and image data that can be copied, and operations beyond this limit will fail.

4 > on the computer to be copied, it should be at least an implied share, that is, the share name is C $or D $... no, no, no.

5 > the WindowsNT account used for the SQLServer 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 those domains.

The movement of SQLServer database is of great significance to the database. To a certain extent, data movement reduces the workload of SQLServer database and improves the work efficiency of SQLServer database. After mastering the above six methods, we can complete the data movement of SQLServer database very well.

At this point, I believe you have a deeper understanding of "what are the mobile methods of SQLServer database?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.