[SQLServer large object] File migration from the FileTable File System

Read and navigate how to migrate files from the file system to FileTable to batch load files to FileTable how to batch load files to FileTable first experience through blog [SQLServer] FileTable, you can load files to the database and view and access these files. To load files to FileTable, you can use xcopy or roboco

Read and navigate how to migrate files from the file system to FileTable to batch load files to FileTable how to batch load files to FileTable first experience through blog [SQLServer] FileTable, you can load files to the database and view and access these files. To load files to FileTable, you can use xcopy or roboco

Read navigation

Migrate files from the file system to the FileTable

Load files to FileTable in batches

How to load files to FileTable in batches

Blog[SQLServer large object] -- FileTable initial experienceYou can load files to the database and view and access these files.

To load files to FileTable, you can use xcopy or robocopy tools, or write scripts (such as PowerShell) or applications to copy files to FileTable.

Now let's talk about file migration.

Migrate files from the file system to the FileTable

File migration conditions

Files are stored in the file system.

In SQL Server, the metadata table contains a pointer to a file.

Execution prerequisites

To migrate a file to FileTable, replace the original UNC path of each file with the UNC path of FileTable.

Now we assume that the existing FileTable PhotoMetadata contains image data ,. This table has a varchar(512 unc, which includes the actual path of the executable .jpg file.

Copy .jpg and its directory structure to the root directory of FileT.

Run

Use the code to modify the metadata of PhotoMetadata:

1: -- add a path locator to PhotoMetadata.

   2:  ALTER TABLE PhotoMetadata ADD pathlocator hierarchyid;

   3:   

4: -- Obtain the root path of the image in the file system.

   5:  DECLARE @UNCPathRoot varchar(100) = '\\RemoteShare\Photographs';

   6:   

7: -- Obtain the root path of the FileTable.

   8:  DECLARE @FileTableRoot varchar(1000);

   9:  SELECT @FileTableRoot = FileTableRootPath('dbo.PhotoTable');

  10:   

11: -- Update PhotoMetadata.

  12:   

13: -- replace the file system UNC path with the FileTable path.

  14:  UPDATE PhotoMetadata

  15:      SET UNCPath = REPLACE(UNCPath, @UNCPathRoot, @FileTableRoot);

  16:   

17: -- Update the pathlocator column of the FileTable.

  18:  UPDATE PhotoMetadata

  19:      SET pathlocator = GetPathLocator(UNCPath);

Load files to FileTable in batches

For batch operations, FileTable is basically the same as other tables, but there are some points to note.

FileTable has system-defined constraints to ensure file integrity and maintainability of the Directory space. These constraints verify that data is loaded to the FileTable in batches. Because many insert operations allow ignore of table constraints, the following is mandatory.

The forced batch loading operation can be used in FileTable as in any other table. The specific operations are as follows:

Bcp includes the CHECK_CONSTRAINTS clause.

Bulk insert carries the CHECK_CONSTRAINTS clause.

Insert... SELECT * from openrowset (BULK ...) Without the IGNORE_CONSTRAINTS clause.

The batch loading operation with unforced constraints will fail, unless the constraints defined by the FileTable system have been disabled, the specific operation is as follows:

Bcp does not contain the CHECK_CONSTRAINTS clause.

Bulk insert does not contain the CHECK_CONSTRAINTS clause.

Insert... SELECT * from openrowset (BULK ...) Contains the IGNORE_CONSTRAINTS clause.

How to load files to FileTable in batches

You can use multiple methods to batch load files to FileTable:

Bcp

Use the CHECK_CONSTRAINTS clause.

Disable the FileTable namespace without using the CHECK_CONSTRAINTS clause. Enable the FileTable namespace again.

BULK INSERT

Use the CHECK_CONSTRAINTS clause.

Disable the FileTable namespace without using the CHECK_CONSTRAINTS clause. Enable FileTable again

Insert... SELECT * from openrowset (BULK ...)

Use the CHECK_CONSTRAINTS clause.

Disable the FileTable namespace without using the CHECK_CONSTRAINTS clause. Enable FileTable again