BCP Data In and Out of a FILEGROUP

There can be several reasons why one would like to “dump” data out, and load data back into a database. In the lifetime of this script I had to use it for 2 requirements; the first time I had to perform a data dump on several hundred databases on various servers in a way compatible with a data retention requirements. The second time I was able to use this script was when I had to move data between a pre-production and production servers that were not in a trust relationship with each other; we could not communicate between the servers directly however the data needed to go from one zone to another and back in the database with minimal down time, and without a restore as all user permissions would get lost and worse, change management would need to be involved....

What does this script do

The script generates the T-SQL to either unload or load the data; it does not do both and it does not perform the actual activity itself. If you are a developer and one can't have you access production data then this script is for you. You can save the generated output and use it in a batch job or schedule a batch job to execute the generated script on the fly.

It is important that you, or create a new script on each meta definition change or generate the script each time you do a dump. Do you know if the meta data definitions have changed since you last generated the output? If not, you might be one of several DBA's or an engineer updated an application that changed the database for you. Then you could first generate the file using the “-o” command line switch of SQLCMD and in a second step execute the generated file.

  • Parameters
  • The Script
Parameters in the script

The script uses parameters, below is an explanation of how to use them.

Parameter Value
@Path C:\Temp This is the storage location (local or network) used for the BCP dump commands, you will needs to have a lot of space!
BatchSize 100000 MS SQL recommended batch size for the BCP command
XmlFormat 0 Can be 0 for false or 1 for true, if true data will be stored in a Xml format, if false then output is generated as text (smaller in size)
SourceServerInstance LocalHost The server (and port) you'd like the generated code to connect to
Security -T Security credential for connecting to the server, possible options are -T (trusted), or -UloginName -PloginPassword
GenerateDump 0 Options are 0 for generating a script that stores data to disk, and 1 for generating a script that is loading data from disk
FileGroup data The name of the file group that is targeted by the script

To download the script click here.

Important to know

This script loads data that is in a given file group, to move data in a given file group you only have to alter the primary clustered index on the table to the file group you need;table data is always stored on the file group that holds the primary clustered index.

Data is stored on the Path parameter, it is best to use a UNC path making sure you actually know where the dada is as mapped drives might be different between server and developers machines.