Database Deployment: The Bits - Copying Data Out

15 November 2012
by Phil Factor

Occasionally, when deploying a database, you need to copy data out to file from all the tables in a database. Phil shows how to do it, and illustrates its use by copying an entire database from one server to another.

Just occasionally, you’ll need to get some or all of the tables of a database out to disk as quickly as possible. I usually do it in order to get a set of data for testing a database. For a full test, I might have a number of different sets of data, and I can do a build-up and tear-down reasonably quickly for each test by importing them into the test database and deleting the tables after use. Naturally, you'll want to copy data in, and I have described how to do this here.  In general, I will keep a consistent set of data that is compatible with the tests, and run the tests with a number of revisions of the database as it approaches completion.  It takes time but then it is done overnight and breeze in the following morning to see a list of tests with ticks or crosses against them. I also occasionally copy a small database to a server when I don’t have sufficient access to the filesystem to copy an MDF file. Oddly enough, I needed to do this via a PowerShell  script on the day I wrote the article you’re reading. I’d never  call this a ‘deployment’ in any sense of the word, but it is a handy way of checking that everything you’ve done works.

 At first glance, there seem to be many more ways of getting data from file into SQL Server fast than there is in getting data out of SQL Server and into files. Actually, there are quite a few techniques one can use, such as OLE automation (deprecated), OrcaMDF,  SSIS, CLR and SQLCMD. In a recent article, I was able to write out the whole of AdventureWorks as  CSV by creating a linked ODBC text database.  I’m not quite sure now why I did it. A similar technique will spit them out as Excel files. As we are not designing a production process in a deployed database, we can use xp_cmdshell and BCP. This is the quickest way of doing it (SSIS uses BCP). The SQLBulkCopy .NET class is fine for importing into SQL Server, but cannot be used to export data. It is perfectly possible to write out all the data of a table using Invoke-Sqlcmd into CSV format or whatever, which is fine for reporting, but it isn't so fast as doing a Bulk Export.

Bulk Export

A BCP export is pretty trivial to do from within SQL Server if your login has the rights to use xp_cmdshell.  Otherwise, you will need to use an external process such as PowerShell or SQLCMD  to do it for you.

If you are copying out all the tables with an eye to copying the database, the problem, of course, is the same as when you do a backup. If you allow insertions or deletion in data that is subject  to referential constraints during the process then the set of table data will cannot be guaranteed to be consistent when you write it all back in. You can fix this, but it is painful and time-consuming.  You have to read the data in to another copy of the database  using BULK INSERT without the CHECK_CONSTRAINTS option set (the default) . All foreign references will be marked as  ‘is_not_trusted.  Then, you’ll have to remove any rows that violate referential integrity.  It is much better to have a consistent set of data on file. You can knock off the other users or make  the database read-only  just for time in which you are  copying of the data out, by ALTER DATABASE database-name SET READ_ONLY and then doing a ALTER DATABASE database-name SET READ_WRITE after the copy is completed.  The coolest way of doing the process is to do  your bulk export  from a database snapshot because  it is Read-only, but you’d need Enterprise edition to do that.

Here is the way you might do it in PowerShell. This saves the files on the machine on which you are running PowerShell. This doesn’t have to be the case. You can save to a network drive or even a UNC, but the process running the PowerShell script has  got to have permission.

<#
A routine to export all the tables from one or more databases in one or more servers to the local client machine in a directory according to the server, database and table-name.
The BCP switches would need to be altered if you have to use SQL Server Security or if you need to save in a different format
  #>

$directory='E:\MyScriptsDirectory' # the directory where you want to store them
$PathToBin='MyPathToBCP' #where BCP is stored
#this can be left blank if your BCP is in the path, but it sometimes isn't
$SourceServer = 'MySourceServer'
$SourceDatabase='MyDatabase' #where we take the data and build script from
$FileType='';
Trap {
  # Handle the error
  $err = $_.Exception
  write-host $err.Message
  while( $err.InnerException ) {
   $err = $err.InnerException
   write-host $err.Message
   };
  # End the script.
  break
  }

#Load SMO assemblies
$MS='Microsoft.SQLServer'
@('.SMO','.SmoExtended') |
     foreach-object {
        if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null)
           {"missing SMO component $MS$_"}
     }
set-psdebug -strict
$ErrorActionPreference = "stop" #

$My="$MS.Management.Smo" #
$s = new-object ("$My.Server") $SourceServer
if ($s.Version -eq  $null ){Throw "Can't find the instance $SourceServer"}
$db= $s.Databases[$SourceDatabase]
if ($db.name -ne $SourceDatabase){Throw "Can't find the database '$SourceDatabase' in $SourceServer"};
$db.Tables |
     foreach {
       $filename = "$($_.schema)_$($_.Name)"  -replace '[\\\/\:\.]','-'
       $TheScriptDirectory = "$directory\$($SourceServer -replace '[\\\/\:\.]','-')\$($_.parent.name -replace '[\\\/\:\.]','-' )"
       if (!(Test-Path -path $TheScriptDirectory ))
         {
         Try { New-Item $TheScriptDirectory -type directory | out-null }
         Catch [system.exception]{
            Write-Error "error while creating '$TheScriptDirectory' "
            return
            }
         }
       $WhatHappened= & "$($pathToBin)BCP.exe"  "$($_)"  out  "$TheScriptDirectory\$filename$filetype" -n -T "-d$($_.parent.name)"  "-S$($_.parent.parent.name)"
       if ($WhatHappened -like '*Error*') {throw ($whatHappened); $whatHappened}
     } 

You can, of course, do it in TSQL on the server, in which case you would have to save the files on the server, remotely specifying a UNC, or using  a network drive mapped to a drive on the server.                                                     

This batch, which can easily be  made into a stored procedure for your ‘utility’ database,  exports, or  BCPs out all the tables from the database.  Most of the effort here is to just check that the file path is valid. The actual BCP operation is pretty trivial, though if you’re used to using a cursor or a WHILE loop, it might raise an eyebrow.  We will be placing our BCP output files in a specially-created directory structure where each database we do has its own subdirectory of its instance( or server)  directory. The batch does all this work for you.

DECLARE @Path VARCHAR(100)

SELECT @Path= 'C:\DataBaseContents\'

DECLARE @FileType VARCHAR(10)

SELECT @FileType= ''-- '.DAT' --remember the dot if you like filetypes

/*This BCPs out all the tables from the database into a location on the local

file system (or network share) given by the @Path variable. Each file is named

after the table and put in subdirectories according to the server and database

*/

 

DECLARE @Command NVARCHAR(MAX) --used to store the BCP commands

DECLARE @MakeDirectoryCommand VARCHAR(255) --one could use one variable but...

DECLARE @ChangeDirectoryCommand VARCHAR(255) --the CD xxxxx command

--these are merely for checking the return strings from executing DOS commands

DECLARE @FirstDosError TABLE (Line_No INT IDENTITY(1,1),line VARCHAR(255))

DECLARE @SecondDosError TABLE (Line_No INT IDENTITY(1,1),line VARCHAR(255))

DECLARE @ThirdDosError TABLE (Line_No INT IDENTITY(1,1),line VARCHAR(255))

--now we check the directory to see if it exists

DECLARE @Directory VARCHAR(255)

SELECT @Directory=@Path--construct a valid path

          +REPLACE(REPLACE(@@ServerName,'\','-'),'/','-')

          +'\'+REPLACE(REPLACE(DB_NAME(),'\','-'),'/','-')+'\',

       @ChangeDirectoryCommand='CD '+@Directory,-- the CD command

       @MakeDirectoryCommand='MKDIR '+@Directory --and the MDIR command

INSERT INTO @FirstDosError (line)

       EXEC master..xp_cmdshell @ChangeDirectoryCommand --try to go there

IF EXISTS ( SELECT * FROM @firstDosError

              WHERE line LIKE 'The system cannot find%' )

  BEGIN --if the directory doesn't exist

    INSERT  INTO @secondDosError ---then make it

            EXEC master..xp_cmdshell @MakeDirectoryCommand

    IF EXISTS ( SELECT * FROM  @SecondDosError

                  WHERE line IS NOT NULL ) -- if that fails

      BEGIN --report the failure and trigger an error

        RAISERROR ('Could perform MKDIR command on %s',16,1,@Directory )

      END

    INSERT INTO @thirdDosError (line) --now try to log in there

            EXEC master..xp_cmdshell @ChangeDirectoryCommand

    IF EXISTS ( SELECT * FROM @ThirdDosError

                  WHERE line LIKE 'The system cannot find%' )

      BEGIN --unlikely, but who knows?

        RAISERROR ('Could NOT create the directory %s',16,1,@Directory )

      END

  END

     

SELECT @Command= --here we construct all the BCP commands

      (select 'exec master..xp_cmdshell ''bcp ' + QUOTENAME(db_name()) + '.'

      +QUOTENAME(sys.schemas.NAME)+'.' + QUOTENAME(sys.tables.name) + ' out "' +@Directory

      +REPLACE(REPLACE(sys.schemas.name+'_'+ sys.tables.name,'.','-'),'\',':')

      + @FileType + '" -S' + @@servername + ' -n -T''

'-- you can change the BCP switches to suit what you want to achieve.

   FROM sys.tables

   LEFT OUTER JOIN sys.schemas

    ON sys.schemas.schema_id = sys.Tables.schema_id

    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')

 

DECLARE @Errors TABLE (line VARCHAR(255))

---this is used to look for BCP errors.

INSERT INTO @Errors

    execute sp_ExecuteSQL @Command --now execute the BCP commands

IF EXISTS ( SELECT * FROM @errors

              WHERE line LIKE 'Error%' OR line LIKE '%is not recognized%' )

  BEGIN --check for any errors and report them

    DECLARE @ErrorMessage VARCHAR(255)

    SELECT TOP 1 @ErrorMessage = line

      FROM @errors --report the first error

        WHERE line LIKE 'Error%'

    IF @errorMessage IS NULL

      BEGIN

        SELECT TOP 1 @ErrorMessage = line

        FROM @errors --or the first report of trouble

        WHERE line LIKE '%is not recognized%'

        RAISERROR (@ErrorMessage, 16,1)

      END

  END

--I like to see the dump of the BCPs output here

SELECT * from @Errors

This takes typically around 15 seconds for AdventureWorks when saving to disk  locally. You can persuade BCP to save across the network but you lose performance, and would probably do better to place it locally and then copy it across later.

Soup to Nuts: Copying a database the hard way.

Of course, copying a database is the heart of database deployment, but it is rare to have to rely on such a method as this, but it works. I did it mainly to check that the component parts of the process that I’ve described both here and in my previous article 'Database Deployment: The Bits - Getting Data In', actually works, by copying the database and then comparing the source with the copy via SQL Compare and SQL Data Compare.  I subsequently needed to use it to copy a small database when remote access didn’t work.  Such is life. SMO has a routine called the TRANSFER task which will do this, and requires much less coding, but it saves and restores the data as insert statements. This means that it is very slow, and I'd feel guilty if I encouraged you to use it to copy a database.

<#  #>
$directory='E:\MyScriptsDirectory' # the directory where you want to store them
$PathToBin='MyPathToBCP' #where BCP is stored
#this can be left blank if your BCP is in the path, but it sometimes isn't
$SourceServer = 'MySourceServer'
$SourceDatabase='MyDatabase' #where we take the data and build script from
$DestinationServer = ("MyDestinationServer") #the destination instance
$DestinationDatabase='MyDatabaseCopy' #the destination database
$ExportData = $false #do we want to export the data from the source database
$CreateScript = $false #do we want created the destination build script from the source database
$buildDestinationDatabase  = $false #do we want to build the destination database from the script
$ImportData= $true #do we want to import the data from file to the destination database?

Trap {
  # Handle the error
  $err = $_.Exception
  write-host $err.Message
  while( $err.InnerException ) {
   $err = $err.InnerException
   write-host $err.Message
   };
  # End the script.
  break
  }

#Load SMO assemblies
$MS='Microsoft.SQLServer'
@('.SMO','.SmoExtended') |
     foreach-object {
        if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null)
           {"missing SMO component $MS$_"}
     }
set-psdebug -strict
$ErrorActionPreference = "stop" #

$My="$MS.Management.Smo" #
$s = new-object ("$My.Server") $SourceServer
if ($s.Version -eq  $null ){Throw "Can't find the instance $SourceServer"}
$db= $s.Databases[$SourceDatabase]
if ($db.name -ne $SourceDatabase){Throw "Can't find the database '$SourceDatabase' in $SourceServer"};
If ($ExportData -eq $true)
   {$db.Tables |
     foreach {
       $filename = "$($_.schema)_$($_.Name)"  -replace '[\\\/\:\.]','-'
       $TheScriptDirectory = "$directory\$($SourceServer -replace '[\\\/\:\.]','-')\$($_.parent.name -replace '[\\\/\:\.]','-' )"
       if (!(Test-Path -path $TheScriptDirectory ))
         {
         Try { New-Item $TheScriptDirectory -type directory | out-null }
         Catch [system.exception]{
            Write-Error "error while creating '$TheScriptDirectory' "
            return
            }
         }
       $WhatHappened=& "$($pathToBin)BCP.exe"  "$($_)"  out  "$TheScriptDirectory\$filename" -n -T "-d$($_.parent.name)"  "-S$($_.parent.parent.name)"
      if ($WhatHappened -like '*Error*') {throw ($whatHappened); $whatHappened}
      }
   }
# and now save the scripts
$TheScriptDirectory="$directory\$($SourceServer -replace '[\\\/\:\.]','-')\$($SourceDatabase -replace '[\\\/\:\.]','-' )\scripts\" #database scripts are local on client

if ($CreateScript -eq $true)
   {
   if (!( Test-Path -path "$TheScriptDirectory" )) #create it if not existing
      {$progress ="attempting to create word directory $TheScriptDirectory"
                 Try { New-Item "$TheScriptDirectory" -type directory | out-null } 
                Catch [system.exception]{
        Write-Error "error while $progress.  $_"
                   return
                   } 
              }
   <# now we will use the canteen system of SMO to specify what we want from the script. It is best to have a list of the defaults to hand and just over-ride the defaults where necessary, but there is a chance that a later revision of SMO could change the defaults so beware! #>
   $CreationScriptOptions = new-object ("$My.ScriptingOptions")
   $CreationScriptOptions.ExtendedProperties= $true # yes we want these
   $CreationScriptOptions.DRIAll= $true # and all the constraints
   $CreationScriptOptions.Indexes= $true # Yup, these would be nice
   $CreationScriptOptions.Triggers= $true # This should be included when scripting a database
   $CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
   $CreationScriptOptions.Filename = "$($TheScriptDirectory)$($sourceDatabase)_Build.sql";
   #we have to write to a file to get the GOs
   $CreationScriptOptions.IncludeHeaders = $true; #of course
   $CreationScriptOptions.ToFileOnly = $true #no need of string output as well
   $CreationScriptOptions.IncludeIfNotExists = $true #not necessary but it means the script can be more versatile
   $transfer = new-object ("$My.Transfer")  $db
   $transfer.options=$CreationScriptOptions #tell the transfer object of pur preferences
   if ("$SourceServer" -ne "$DestinationServer")
      { @"
   use Master
   IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'
$SourceDatabase')
     CREATE DATABASE [
$SourceDatabase] 
   Go
"@
 > "$($TheScriptDirectory)$($sourceDatabase)_Build.sql"
      #"$SourceServer -ne $DestinationServer"
      }
   else
      { remove-item "$($TheScriptDirectory)$($sourceDatabase)_Build.sql"}
   $scripter = new-object ("$My.Scripter") $s #script out the database creation
   $scripter.options=$CreationScriptOptions #with the same options
   $scripter.options.AppendToFile=$true
   $scripter.Script($db) #do it
   if ($SourceDatabase -ne $DestinationDatabase) {
      #now we alter the name to create the copy. This isn't safe with certain database names
      if ($DestinationDatabase -eq $null) {$DestinationDatabase="New$Database"}
      $TheBuildScript = [string]::join([environment]::newline, (Get-content ("$($TheScriptDirectory)$($sourceDatabase)_Build.sql")))
      $TheBuildScript=$TheBuildScript -replace $sourceDatabase, $DestinationDatabase
      $TheBuildScript > "$($TheScriptDirectory)$($sourceDatabase)_Build.sql"
      }
     
   "USE [$DestinationDatabase]"  >> "$($TheScriptDirectory)$($sourceDatabase)_Build.sql"
   # add the database object build script
   #$transfer.DestinationDatabase=$DestinationDatabase
   #$transfer.CreateTargetDatabase=$true
   #$transfer.DestinationServer=$DestinationServer
   #$transfer.DestinationLoginSecure=$true
   $transfer.options.AppendToFile=$true
   $transfer.options.ScriptDrops=$true
   $transfer.EnumScriptTransfer()
   $transfer.options.ScriptDrops=$false
   $transfer.EnumScriptTransfer()
   "All written to $($TheScriptDirectory)$($sourceDatabase)_Build.sql"
   Trap [System.Data.SqlClient.SqlException] {
      Write-Host "A SQL Error occurred:`n" + $_.Exception.Message
      break
      }
   }

$s = new-object ("$My.Server") $DestinationServer
if ($s.Version -eq  $null ){Throw "Can't find the instance $DestinationServer"}
if ($buildDestinationDatabase -eq $true)
   {
   $master = New-Object ("$My.Database") ($s, 'master')
   # what we do if there is a sql info message such as a PRINT message
   $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.source  ': ' $event.Message };
   $s.ConnectionContext.add_InfoMessage($handler);
   $TheBuildScript = [string]::join([environment]::newline, (Get-content ("$($TheScriptDirectory)$($sourceDatabase)_Build.sql")))
   $result=$master.ExecuteNonQuery($TheBuildScript )
   }
$SQL=@'
   SET NOCOUNT ON;
   select   '"' +TABLE_CATALOG + '"."'+Table_Schema+'"."' + Table_Name+'"'  QualifiedTableName,
        Table_Schema+'_' + Table_Name FileTableName
   FROM information_schema.Tables WHERE table_Type='BASE TABLE'

'@

# now get the destination database object
$Destination = New-Object ("$My.Database") ($s, "$DestinationDatabase")
if ($Destination.name -ne $DestinationDatabase) #on failure
     {Throw "Can't find the database '$DestinationDatabase' in $DestinationServer"};
#now get the message handler to get SQL messages
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};
#and assign it to the message pipeline. We just choose to show it in the output
$s.ConnectionContext.add_InfoMessage($handler);
# execute the SQL to get the tables into a dataset
$result=$Destination.ExecuteWithResults("$SQL") #execute the SQL
# and disable triggers just in case one interferes with the ipmort
$Destination.ExecuteNonQuery("Execute sp_msforeachtable  'ALTER TABLE ? DISABLE TRIGGER all' ")
#now, for each table in the pipeline
$result.Tables[0] |
  foreach {
          $filename = "$($_.FileTableName)"  -replace '[\\\/\:\.]','-' #work out the name of the filename
          #and now we get the whole path to the file we want to import
          $TableSource = "$directory\$($SourceServer -replace '[\\\/\:\.]','-')\$($SourceDatabase -replace '[\\\/\:\.]','-' )"
          #and we execute the BCP command
          $WhatHappened=&"$($pathToBin)BCP.exe" "$($_.QualifiedTableName)"  in  "$TableSource\$filename" -q -n -T -E   "-S$($DestinationServer)"
if ($WhatHappened -like '*Error *') {throw ($WhatHappened)}
          }
#all is now in place. We just have to set the contraints to trusted
$Destination.ExecuteNonQuery(@"
  EXECUTE sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'  
  EXECUTE sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
"@ ) #flag constraints as trusted
$s.ConnectionContext.remove_InfoMessage($handler);
  


© Simple-Talk.com