Carefully import and export bulk data in SQL Server

In the process of database initialization, a real problem that administrators need to face is how to import large capacity data into the database system. A number of large-capacity data import and export tools are available in the SQL Server database for database administrators to be useful. Administrators can implement bulk export data and bulk import data and generate format files through the bcp utility. If you can use the BULK INSERT statement to import bulk data directly from a data file into a database table or an unpartitioned view, and so on.

Although these utilities are available in the SQL Server database. However, in practical work, administrators still need to be cautious about this work. Specifically, in the process of large-capacity data import and export, we need to pay attention to the following aspects.

First, try to use the tools provided by the system to pour out large volume of data

The above mentioned several practical tools, they have a common feature, that is, in the process of import will be large capacity of the data for a certain optimization. If you can standardize some data format, save time to import export, and so on. But using these tools, there is a difference with other common data import export tools, that is, the bulk-import operation does not support importing data in a comma-delimited file, which is often said as a text file. Although administrators can also use other tools to import bulk data in file formats, this is usually not recommended by the author. Because other tools do not support optimization in the import process. To this end, I suggest using other tools, such as access and other intermediary tools to first convert the data in the text file into a normal form, and then use the above tools to import into the system. Although this will increase a certain amount of work, but it can guarantee the quality of large-capacity data. To this end, the author in the actual work, if the user encountered this problem, I strongly recommend that they should use the tools provided by the system to import the export of large-capacity data.

In addition, the use of format files to improve the standardization of large-capacity data is also a good choice. Several of the bulk import export tools mentioned above support the use of specialized format files to store format information for each field in the original data file. The format file can also contain information about the corresponding database table. A format file can be used to provide all the formatting information you need to bulk export data from a database instance and to bulk import data into it. In layman's terms, format files provide a flexible way of interpreting the format of data in data files during import and setting data formats in data files during export. This flexibility eliminates the need to write specialized code for interpreting data, and the need to reformat data to meet the special needs of a database or external application. If you are flexible with formatting files, you can export or import large volumes of data directly in the format you want without having to make additional formatting adjustments later.

Second, choose the appropriate database log operation mode

As you know, any changes that users make in the database will be recorded in the relevant log. Importing and exporting large volumes of data is no exception. However, because of large capacity data, it will occupy a relatively large transaction log function. To this end, I suggest that before users import large data, it is best to choose the appropriate database log operation mode. The author's approach is that if users need to import bulk data, then it is best to choose the bulk-logged recovery model. Wait until the import is complete, and then revert to the original mode.

This is mainly because in the bulk-logged mode, for large-capacity data import work said that support is relatively good. Compared to other log recovery modes (such as the full recovery model), the bulk-logged recovery model minimizes records for bulk operations only. The bulk-logged Operation Recovery Model protects the bulk operation from hardware failure, providing better performance and minimizing the space occupied by the log. Therefore, using bulk-logged recovery helps prevent the transaction log from being out of space because bulk-logged recovery does not insert journal lines. This bulk-logged operation pattern is ideal for databases that use the full recovery model. The bulk-logged recovery model is useful when performing bulk operations on an index-free table.

However, the bulk-logged operating mode also has a certain risk. such as the bulk-logged recovery model increases the risk of data loss for these bulk copy operations. Because the bulk-logged operation mode will prevent the database system from capturing changes made to each transaction. If a log backup contains bulk-logged operations, you cannot restore to the point in time in the log backup, only the entire log backup can be restored. In addition, under the bulk-logged recovery model, if a log backup overwrites any bulk operation, the log backup contains the log records and data pages that were changed by the bulk operation. This is critical for capturing the results of bulk-logged operations. The merged data area makes log backups very large. Furthermore, the backup log requires access to the data file that contains the bulk-logged transaction. If you cannot access any of the affected database files, the transaction log will not be backed up, and all actions committed in this log are lost. Therefore, the bulk-logged backup mode is not a secure log mode.