Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

Extracting binary data from SQL Server


In this recipe, we will extract the binary content from SQL Server and save it back to individual files in the filesystem.

Getting ready

For this recipe, we will use the table that we created in the previous recipe, Storing binary data in SQL Server, to extract files. Feel free to use your own tables that have the VARBINARY(MAX) columns; just ensure that you change the table name in the script.

In addition to our SampleBLOB table, we will create an empty table with a single VARBINARY(MAX) table. We will use this to facilitate the creation of a format file we need to export binary data out of SQL Server using bcp:

USE SampleDB
GO
IF OBJECT_ID('EmptyBLOB') IS NOT NULL
    DROP TABLE EmptyBLOB
GO
CREATE TABLE EmptyBLOB
(
    BLOBStuff VARBINARY(MAX)
)

How to do it...

These are the steps required to extract binary data from SQL Server:

  1. Open PowerShell ISE as an administrator.

  2. Import the SQLPS module as follows:

    #import SQL Server module
    Import-Module SQLPS -DisableNameChecking...