A helpful way to import or export data from SQL Server to bcp.exe binary flat files. This can be helpful for storing relatively small sets of test data in source control, dropbox, etc for multiple environments.

TestData.cmd

:: Name:     TestData.cmd
:: Purpose:  Convenience script to use SQL Server bcp.exe to import or export
::           sample data as binary files in ~/src/Database/TestData
:: Author:   Steve Jansen
:: Revision: December 2012

@ECHO OFF
SETLOCAL ENABLEEXTENSIONS

:: default values
SET me=[%~n0]
SET project=%~dps0..
:: normalize the project path
PUSHD %project% >NUL
SET project=%CD%
POPD >NUL
SET args=%*
SET log="%TEMP%\%~n0.log"
SET cygwin=
SET filename=
SET action=export
SET server=.\SQLExpress
SET database=
SET table=
SET user=
SET password=
SET query=
SET maxrows=

:: parameter checking
ECHO.%* | FINDSTR /I "^-? ^/? ^-help ^/help" >NUL 2>&1 && GOTO :usage

:: environment validation
BCP.exe /? >NUL
IF /I %ERRORLEVEL%==9009 (
  ECHO %me%: ERROR - SQL Server bcp.exe utility not found in PATH
  EXIT /B 2
)

SQLCMD.exe /? >NUL
IF /I %ERRORLEVEL%==9009 (
  ECHO %me%: ERROR - SQL Server sqlcmd.exe utility not found in PATH
  EXIT /B 2
)

IF DEFINED ProgramFiles(x86) (
  SET cygwin="%ProgramFiles(x86)%\Git\bin\sh.exe" -login -c
) ELSE (
  SET cygwin="%ProgramFiles%\Git\bin\sh.exe" -login -c
)

%cygwin% pwd >NUL
IF /I NOT %ERRORLEVEL%==0 (
  ECHO %me%: WARNING - Cygwin not found, gzip compression disabled for binary files
  SET cygwin=
)

:: command line argument parsing
:args
SET arg=%~1
:: normalize "/X" switches as "-X"
SET arg=%arg:/=-%
:: skip to the main processing block if no args left to process
IF "%arg%"== "" GOTO :main

IF /I "%arg%"=="import" (
  SET action=import
  SHIFT
  GOTO :args
)

IF /I "%~1"=="export" (
  SET action=export
  SHIFT
  GOTO :args
)

IF /I "%arg%"=="-S" (
  SHIFT
  SET server=%~2
  SHIFT
  GOTO :args
)

IF /I "%arg%"=="-D" (
  SHIFT
  SET database=%~2
  SHIFT
  GOTO :args
)

IF /I "%arg%"=="-U" (
  SHIFT
  SET user=%~2
  SHIFT
  GOTO :args
)

IF /I "%arg%"=="-P" (
  SHIFT
  SET password=-P "%~2"
  SHIFT
  GOTO :args
)

IF /I "%arg%"=="-T" (
  SHIFT
  SET table=%~2
  SHIFT
  GOTO :args
)

IF /I "%arg%"=="-N" (
  SHIFT
  SET maxrows=%~2
  SHIFT
  GOTO :args
)

IF /I "%arg%"=="-Q" (
  SHIFT
  SET query=%~2
  SHIFT
  GOTO :args
)

:main
IF NOT "%maxrows%"=="" SET maxrows=-L %maxrows%

:: prompt for any mandatory properties that were not passed as command line arugments
IF NOT DEFINED database (
  SET /P database=%me%: enter the sql server database name for %action%^>
  GOTO :main
)

IF NOT DEFINED user (
  ECHO %me%: sql server username not specified
  ECHO %me%: defaulting to integrated Windows authentication
  SET user=-T
) ELSE (
  SET user=-U "%user%"
)

:: tracing printout
ECHO [--- ENVIRONMENT TRACE ---]
ECHO [CMDCMDLINE]: [%CMDCMDLINE%]
ECHO [ARGS]:       [%*]
ECHO [CD]:         [%CD%]
ECHO [cygwin]:     [%cygwin%]
ECHO [action]:     [%action%]
ECHO [project]:    [%project%]
ECHO [server]:     [%server%]
ECHO [database]:   [%database%]
ECHO [table]:      [%table%]
ECHO [user]:       [%user%]
ECHO [password]:   [%password%]
ECHO [maxrows]:    [%maxrows%]
ECHO [query]:      [%query%]
ECHO [--- ENVIRONMENT TRACE ---]


IF "%action%"=="import" GOTO :import
IF "%action%"=="export" GOTO :export

REM this line should be skipped
ECHO %me%: unknown action "%action%"
EXIT /B 4

:export
IF NOT DEFINED table (
  ECHO %me%: generating list of all user tables
  SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "SET NOCOUNT ON; SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + OBJECT_NAME([object_id]) AS [FullName] FROM [sys].[tables] ORDER BY [FullName]" -h -1 -o "%TEMP%\%~n0.txt"  || GOTO :ERROR
) ELSE (
  ECHO %table% > "%TEMP%\%~n0.txt"
)

FOR /F %%I IN (%TEMP%\%~n0.txt) DO (
  CALL :bcp-export "%%~I" || GOTO :ERROR
)
GOTO :END

:import
SET filename=%table:dbo.=%
SET filter=%filename%.bcp*

IF NOT DEFINED table (
  SET filter=*.bcp*
)

ECHO %me%: disabling all constraints in all tables
SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "EXEC sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'" || GOTO :ERROR

FOR %%I IN (%filter%) DO (
  CALL :bcp-import "%%~nI" || GOTO :ERROR
)

ECHO %me%: enabling all constraints in all tables
SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "EXEC sp_MSforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT ALL'" || GOTO :ERROR

IF DEFINED query (
  ECHO %me%: executing post-import query "%query%"
  SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "%query%" || GOTO :ERROR
)
GOTO :END

:ERROR
ECHO %me%: exiting due to error
SET /A ERRORLEVEL = %errno% + %ERRORLEVEL%

:END
DEL /Q %log% >NUL 2>&1
ENDLOCAL
ECHO ON
@EXIT /B %errno%


:: functions
::#############################################################################
:normalize
SET table=%~1
REM strip the default schema "dbo" from the filename
SET filename=%table:dbo.=%
EXIT /B 0


::#############################################################################
  :bcp-export
CALL :normalize "%~1"

ECHO %me%: exporting table %table%

REM see http://msdn.microsoft.com/en-us/library/ms191516.aspx for more info on bcp.exe
REM recreate the format file first
BCP.exe "%table%" FORMAT nul -S "%server%" -d "%database%" %user% %password% -n -f "%filename%.fmt" > %log%
SET /A errno = %errno% + %ERRORLEVEL%
IF NOT "%errno%"=="0" (
  FINDSTR /I /B "Error" %log%
  EXIT /B %errno%
)

REM export the rows in binary format
BCP.exe "%table%" OUT "%filename%.bcp" -S "%server%" -d "%database%" %user% %password% -f "%filename%.fmt" %maxrows% > %log%
SET /A errno = %errno% + %ERRORLEVEL%
IF NOT "%errno%"=="0" (
  FINDSTR /I /B "Error" %log%
  EXIT /B %errno%
)

IF DEFINED cygwin (
  %cygwin% "gzip --force --best '%filename%.bcp'" >NUL
)
SET /A errno = %errno% + %ERRORLEVEL%

EXIT /B %errno%


::#############################################################################
:bcp-import
CALL :normalize "%~1"
SET table=%table:.bcp=%
SET filename=%filename:.bcp=%

ECHO %me%: importing data into table %table%

IF NOT EXIST "%filename%.fmt" (
  ECHO %me%: WARNING - skipping table %table% - file not found - %filename%.fmt
  EXIT /B 1
)

IF DEFINED cygwin (
  IF EXIST "%filename%.bcp.gz" (
    %cygwin% "gzip --force --best --decompress --stdout '%filename%.bcp.gz' > '%filename%.bcp'" >NUL
  )
)
SET /A errno = %errno% + %ERRORLEVEL%

SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "DELETE FROM %table%" >NUL
SET /A errno = %errno% + %ERRORLEVEL%

BCP.exe "%table%" IN "%filename%.bcp" -S "%server%" -d "%database%" %user% %password% -E -f "%filename%.fmt" > %log%
SET /A errno = %errno% + %ERRORLEVEL%
IF NOT "%errno%"=="0" (
  FINDSTR /I /B "Error" %log%
  EXIT /B %errno%
)

IF DEFINED cygwin (
  IF EXIST "%filename%.bcp" (
    DEL /Q "%filename%.bcp" >NUL
  )
)

EXIT /B %errno%


::#############################################################################
: usage
ECHO.
ECHO %me%
ECHO.  Import or export database sample data to/from binary bulk copy files
ECHO.  in the current working directory
ECHO.
ECHO.Usage:
ECHO. %~nx0 [import^|export] -S server -D database [-U username] [-P password]
ECHO. [-T table] [-N maxrows] [-Q query]
ECHO.
ECHO.  [import^|export]  defaults to %action% when not specified
ECHO.  -S  the SQL Server host/instance name, defaults to %server%
ECHO.  -D  the SQL Server database name, required
ECHO.  -U  defaults to Windows Authentication as %%USERNAME%%
ECHO.  -P  password, not required for Windows Authentication,
ECHO.      otherwise will prompt for password on each table if not specified
ECHO.  -T  table name to target, defaults to all tables for export,
ECHO.      or all bcp files for import
ECHO.  -N  the maximum number of rows to export, defaults to all rows
ECHO.  -Q  custom sql query to execute after import; ignored for export
SET /A errno=1
GOTO :END
EXIT /B 0