In this series of articles on SQL Server FILESTREAM (see TOC at bottom), we explored various ways to store unstructured data in the file system with the metadata in SQL Server tables. If we have a large number of objects in the file system, it is advisable to use the fast disk for storage purpose. It is faster and provides better IO in comparison with the traditional file system.
In my previous article, Importing SQL Server FILESTREAM data with SSIS packages, we used SSIS packages to import the files into SQL Server FILESTREAM table. Sometimes, we want to export FILESTREAM objects to different drive. We might need to copy the complete data or particular data from the file system. Even though you might think that we can copy the files directly by accessing the file system. Let us first look at the issues with this approach.
Suppose we have the following files stored in the SQL Server FILESTREAM container. First, we do not know the exact name of the files. SQL Server does not store the files in their original name. Instead, it assigns the names as per an internal mechanism.
Let us say we just want to copy few files from SQL Server FILESTREAM container to local disk. We copied the above-highlighted objects into different drive.
Once we have copied the files into a different drive, we’ll try to open it, but the OS does not recognize the extension of these files. If we try to open it, it asks us to select the program in which we want to open the file.
We do not necessarily know the appropriate type of this file, therefore; let us open with the Notepad. It does not open in the correct format because it is an image file. Further complicating things, it is difficult to identify the file type because it does not have any file extension.
In this article, we want to demonstrate how to export the images as per the following sources and destinations.
- SQL Instance: .\SQL2019
- Source FILESTREAM Database: FileStreamDemoDB_test
- Source FILESTREAM table: [Tbl_Support_Documents]
- Destination Folder: ‘C:\sqlshack\Objects’
- Object Count: 343
PowerShell is a very powerful management tool that can interact with SQL Server easily and do administrative tasks with minimal configurations. We can use PowerShell to interact with the SQL Server FILESTREAM and export the objects into the destination folder.
To fully understand this step, we need to review the PowerShell script in parts. Once we have covered the script, we will use the combined script to export the objects in Windows PowerShell. I have taken this script from Microsoft TechNet and modified it as per our requirement.
- Define the parameters for connection to SQL Server: In this part, we will define the variable and pass the required input. In the following command, you will notice the SQL Server instance details, database name in which FILESTREAM table exists, destination folder. We also need to define the buffer size, let us define is as 16,384 bytes.
|
1 2 3 4 |
$Server = ".\sql2019"; $Database = "FileStreamDemoDB_test"; $Dest = "C:\Export\"; $bufferSize = 8192; |
- Specify the SQL Server statement to fetch details from SQL Server FILESTREAM table: In the Second step, we need to specify the query to retrieve information from the FILESTREAM table
|
1 2 3 |
$Sql = "SELECT [Document_Name],[DocumentBin] FROM [FileStreamDemoDB_test].[dbo].[Tbl_Support_Documents] "; |
It is the same query that we can run in SSMS to retrieve the information from the FILESTREAM table.
- Open a database connection and specify the credentials to connect with the instance: In this part, we want to connect to the Database with the windows authentication (integrated security). In this article, we are using windows authentication.
Note: user should have sufficient permission to connect to the specified Database instance and query the FILESTREAM table.
|
1 2 3 4 5 |
$con = New-Object Data.SqlClient.SqlConnection; $con.ConnectionString = "Data Source=$Server;" + "Integrated Security=True;" + "Initial Catalog=$Database"; $con.Open(); |
- Write a message to show that the export process started: We can use the PowerShell command ‘write-output’ to print the required message.
|
1 |
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Export FILESTREAM objects Started ..."); |
- Create the command and execute the reader: Now, we will open a new command to execute the query, we specified in step 2, on SQL Server Database. We might also specify the connection timeout in seconds however here for simplicity we will avoid using that.
|
1 2 |
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con; $rd = $cmd.ExecuteReader(); |
- Create a byte array and loop through the records in the table: In this step, we are going to loop through each row in the SQL Server FILESTREAM table. We will use ‘System.IO.FileStream’ FILESTREAM class to provide synchronous read-write along with the synchronous method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
$out = [array]::CreateInstance('Byte', $bufferSize) While ($rd.Read()) { try { Write-Output ("Exporting Objects from FILESTREAM container: {0}" -f $rd.GetString(0)); # New BinaryWriter $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write; $bw = New-Object System.IO.BinaryWriter $fs; $start = 0; # Read first byte stream $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); While ($received -gt 0) { $bw.Write($out, 0, $received); $bw.Flush(); $start += $received; # Read next byte stream $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); } $bw.Close(); $fs.Close(); } catch { Write-Output ($_.Exception.Message) } finally { $fs.Dispose(); } } |
- Closing the objects: We need to close all the objects in this step and print the success message.
|
1 2 3 4 5 6 |
$rd.Close(); $cmd.Dispose(); $con.Close(); Write-Output ("Finished"); Read-Host -Prompt "Press Enter to exit" |
Now let us combine all parts of the script in a single file.
Note: You need to change the connection parameters and the destination folder path as per your requirement before executing the script. The complete script to copy the SQL Server FILESTREAM data into file system is as below.
Launch the Windows PowerShell from the start menu.
Now execute the query in PowerShell. It will start copying the files from the SQL Server FILESTREAM to the desired folder specified in the script. Script execution may take time depending upon the number of objects, their size, network bandwidth. You get the printed message in the PowerShell window for each file that is copied to the folder.
Once the script completes the files export from FILESTREAM container, you get the message ‘Finished’. We need to press ‘Enter’ to exit.
We can verify the files from the destination folder. Below you can the files are now present in this folder with the actual file name and the extension. You can open the files, and OS recognize these files. Therefore, you can see the icon image of each file type as well.
Use an SSIS package to Import and Export FILESTREAM objects
In the previous article, Importing SQL Server FILESTREAM data with SSIS packages, we created an SSIS package to import the files into SQL Server FILESTREAM tables. We want to use the same package to export the objects into the destination folder as well.
Therefore, we will use this SSIS package to execute the PowerShell script as well. Drag the ‘Execute Process Task’ in the control flow task.
Double click on ‘Execute Process Task’ and it opens the execute process task editor. In this, rename the task to reflect its usage. We renamed it to ‘Export FILESTREAM Object’.
We saved the PowerShell script with ‘.PS1’ extension to save it in the Windows PowerShell Script format.
In the execute process task editor, go to Process and provide the below parameters.
- Executables:C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
- Arguments: -ExecutionPolicy ByPass -command “. ‘C:\sqlshack\Draft articles\ExportFILESTREAM.PS1′”
Click ‘Ok’, and our SSIS package looks as shown below.
Before we execute the SSIS package to do the import and export together, truncate the existing FILESTREAM table. We have only 13 files in the source folder ‘ C:\images’ to keep this execute quick. We will also remove all the files from the ‘C:\Export’ folder.
|
1 |
Truncate table [FileStreamDemoDB_test].[dbo].[Tbl_Support_Documents] |
The follow task will execute in the SSIS package.
- Import files from ‘C:\Images’ folder to SQL Server FILESTREAM table Tbl_Support_Documents
- Export files from FILESTREAM container to ‘C:\Export’ folder using the PowerShell Script.
Now, we are ready to execute this SSIS package. Execute this package and monitor it. Once it reaches the ‘Export FILESTREAM object’, it launches the PowerShell Script window as shown below.
The SSIS package is successful now for both the import and the export tasks.
Let us perform the validations. We can see data in the FILESTREAM table (13 rows). It shows that Import is successful.
Now go to the export folder ‘ C:\Export’ and view the 13 files. It shows that the export task is also successful.
Conclusion
We explored the task to export FILESTREAM objects into the file system directory using aPowerShell script and the SSIS package. We will continue covering the more topics on the SQL Server FILESTREAM in upcoming articles.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023