Import-DbaBinaryFile

Want to see the source code for this command? Check out Import-DbaBinaryFile on GitHub.
Want to see the Bill Of Health for this command? Check out Import-DbaBinaryFile.

Synopsis

Loads binary files from the filesystem into SQL Server database tables

Description

Reads binary files from disk and stores them in SQL Server tables with binary, varbinary, or image columns. This is useful for storing documents, images, executables, or any file type directly in the database for archival, content management, or application integration scenarios.

The command automatically detects the appropriate columns for storing file data - it looks for binary-type columns (binary, varbinary, image) for the file contents and columns containing "name" for the filename. You can also specify exact column names or provide a custom INSERT statement for more complex scenarios.

Files can be imported individually, from directories (with recursion), or piped in from Get-ChildItem. Each file is read as a byte array and inserted using parameterized queries to safely handle binary data of any size within SQL Server's limits.

Syntax

Import-DbaBinaryFile
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String>]
    [[-Table] <String>]
    [[-Schema] <String>]
    [[-Statement] <String>]
    [[-FileNameColumn] <String>]
    [[-BinaryColumn] <String>]
    [-NoFileNameColumn]
    [[-InputObject] <Table[]>]
    [[-FilePath] <FileInfo[]>]
    [[-Path] <FileInfo[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-ChildItem C:\photos | Import-DbaBinaryFile -SqlInstance sqlcs -Database employees -Table photos

Imports all photos from C:\photos into the photos table in the employees database on sqlcs. Automatically guesses the column names for the image and filename columns.

Example: 2

PS C:\> Import-DbaBinaryFile -SqlInstance sqlcs -Database tempdb -Table BunchOFiles -FilePath C:\azure\adalsql.msi

Imports the file adalsql.msi into the BunchOFiles table in the tempdb database on sqlcs. Automatically guesses the column names for the image and filename columns.

Example: 3

PS C:\> Import-DbaBinaryFile -SqlInstance sqlcs -Database tempdb -Table BunchOFiles -FilePath C:\azure\adalsql.msi -FileNameColumn fname -BinaryColumn data

Imports the file adalsql.msi into the BunchOFiles table in the tempdb database on sqlcs. Uses the fname and data columns for the filename and binary data.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies the target database where the binary files will be imported. Required when not using InputObject.
Use this to identify which database contains the table for storing your binary files.

-Table

Specifies the target table where binary files will be stored. Must contain at least one binary-type column (binary, varbinary, image).
Use this when importing files into a specific table designed for file storage. Supports three-part naming (db.schema.table).
If the object has special characters please wrap them in square brackets [ ].
Using dbo.First.Table will try to find table named 'Table' on schema 'First' and database 'dbo'.
The correct way to find table named 'First.Table' on schema 'dbo' is by passing dbo.[First.Table]
Any actual usage of the ] must be escaped by duplicating the ] character.
The correct way to find a table Name] in schema Schema.Name is by passing [Schema.Name].[Name]]]

-Schema

Specifies the schema containing the target table. Defaults to the user's default schema if not specified.
Use this when your table exists in a non-default schema or when you need to be explicit about schema ownership.

-Statement

Provides a custom INSERT statement for complex import scenarios. Must include @FileContents parameter for binary data.
Use this when automatic column detection fails or when you need custom INSERT logic with joins, triggers, or computed columns.
Example: INSERT INTO db.tbl ([FileNameColumn], [bBinaryColumn]) VALUES (@FileName, @FileContents)
The @FileContents parameter is required. Include @FileName parameter if storing filenames.

-FileNameColumn

Specifies which column will store the original filename. Auto-detects columns containing 'name' if not specified.
Use this when your table has multiple name-related columns or when auto-detection fails to identify the correct column.

-BinaryColumn

Specifies which column will store the binary file data. Auto-detects binary, varbinary, or image columns if not specified.
Use this when your table has multiple binary columns or when auto-detection fails to identify the correct storage column.

-NoFileNameColumn

Indicates that the target table does not have a column for storing filenames. Only the binary data will be imported.
Use this when your table design only stores file content without filename metadata for blob storage scenarios.

-InputObject

Accepts table objects from Get-DbaDbTable for pipeline-based imports. Alternative to specifying Database and Table parameters.
Use this when working with multiple tables or when integrating with other dbatools commands that return table objects.

-FilePath

Specifies one or more individual files to import into the database table. Accepts pipeline input from Get-ChildItem.
Use this when importing specific files rather than entire directories. Cannot be used with Path parameter.

-Path

Specifies a directory containing files to import. Recursively processes all files within the directory and subdirectories.
Use this when bulk importing multiple files from a folder structure. Cannot be used with FilePath parameter.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed

-Confirm

Prompts you for confirmation before executing any changing operations within the command