CODEWITHSUNDEEP
sql-serverdatabaseimportcmdexport
MichaelS
MichaelS

Reputation: 6042

How to transfer data from a newer to an older MS SQL server using cmd?

I have a database server (let's call it S) with some data running MS SQL 2012. On the other end of the world there are tablets running MS SQL 2012 EXPRESS (let's call them TS). A part of the data must be transferred from S to TS. Due to many reasons the only way to communicate is to send files from S to proxy servers to have them picked up by TS later.

Currently I'm using backup files (.BAK) so I can simply create a partial backup of S' database and restore it on TS.

Now here is the problem:

It might happen that in a while S will switch to MS SQL 2014. It's not possible to install 2014 on TS because I have no direct access to them. In this case S will still be able to restore data created with SQL 2012 on TS but not the other way around because it's impossible to restore backups from a newer SQL version on an older one. So this is the point I need help in.

I've tried to export the data using the server management tool on S instead of creating a backup and import everything on TS. This works well but the export must happen automatically per command line. The good old sqlpubwiz is not an option because it was discontinued and only works up to version 2008 R2.

So I need ideas how to export from S using cmd/powershell and import on TS only using files to communicate even if they are running different versions of MS SQL Server.

Upvotes: 1

Views: 288

Answers (3)

David Roussel
David Roussel

Reputation: 5916

I've used detatched databases before to distribute databases to laptops before. They are much quicker to import than a backup, but I'm not sure about inter-version compatibility.

You could write a tool that you package up as an exe that contains CSV files and a loader script which uses Bcp.exe

Upvotes: 1

MichaelS
MichaelS

Reputation: 6042

Problem solved! Powershell does the trick but it was way too slow. At the end I've still used BCP. BCP means a lot of scripting to do but works about 30 times faster than using powershell. However, here is the powershell script I've used:

set-psdebug -strict
$DirectoryToSaveTo='...' #path where to save the data
$ServerName='...' #Hostname\Instance
$Database='...' #database to export
$ErrorActionPreference = "stop"
Trap {
    $err = $_.Exception
    write-host $err.Message
    while( $err.InnerException ) {
        $err = $err.InnerException
        write-host $err.Message
    };
    break
}
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
}
$My='Microsoft.SqlServer.Management.Smo'
$s = new-object ("$My.Server") $ServerName
$Server=$s.netname -replace '[\\\/\:\.]',' '
$instance = $s.instanceName -replace '[\\\/\:\.]',' '
$DatabaseName =$database -replace '[\\\/\:\.]',' '
$DirectoryToSaveTo=$DirectoryToSaveTo+'\'+$Instance+'\'
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true
$CreationScriptOptions.DRIAll= $true
$CreationScriptOptions.Indexes= $true
$CreationScriptOptions.Triggers= $true
$CreationScriptOptions.ScriptBatchTerminator = $true
$CreationScriptOptions.Filename = "$($DatabaseName)_Schema.sql";
$CreationScriptOptions.IncludeHeaders = $true;
$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") $s.Databases[$Database]
$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$scripter = new-object ("$My.Scripter") $s # script out the database creation
$scripter.options=$CreationScriptOptions # with the same options
$scripter.Script($s.Databases[$Database]) # do it
"USE $Database" | Out-File -Append -FilePath "$($DatabaseName)_Schema.sql"
"GO" | Out-File -Append -FilePath "$($DatabaseName)_Schema.sql"
$transfer.options.AppendToFile=$true
$transfer.options.ScriptDrops=$true
$transfer.EnumScriptTransfer()
$transfer.options.ScriptDrops=$false
$transfer.EnumScriptTransfer()
"All written to $($DatabaseName)_Schema.sql"

This script exports only the schema but it should be quite obvious how to export also all the data.

Upvotes: 0

podiluska
podiluska

Reputation: 51494

I would look at using the built-in replication features, setting up S as a snapshot replication publisher, and TS as a subscriber via FTP. SQL Server Express can act as a subscriber (but not a published) in a replication scenario.

http://msdn.microsoft.com/en-us/library/ms151832.aspx

If you want to write a more intricate scenario, you may want to consider Sync Framework

Upvotes: 0

Related Questions