En testant SQL Azure on se retrouve facilement confronter au problème de transfert des données d’une base de données interne à l’entreprise vers un serveur SQL Azure dans le Cloud. En effet, il n’y a pas de commandes BACKUP / RESTORE ou encore sp_detach_db / sp_attach_db. Les possibilités qui s’offrent à nous sont maintenant restreintes avec SSIS, BCP, l’assistant d’import / export de SQL Server ou encore les API de programmation.

BCP est certainement une des méthodes de chargement les plus rapides mais l’inconvénient majeur est que celui-ci ne s’exécute qu’au niveau table. En d’autres termes on ne peut pas extraire les données d’une base en lançant une seule commande BCP. Il faut créer une commande BCP pour chaque table de la base. Même chose lors’qu’il s’agit d’import … Autant dire que l’opération peut être rapidement fastidieuse !!! Le script suivant permet d’exporter et d’importer l’ensemble des données d’une base interne vers SQL Azure de façon automatique. Pour obtenir l’aide il suffit de lancer la commande .\votrefichier.ps1 seule. Attention ce script ne vérifie pas les paramètres d’entrées. Vous pouvez toujours l’améliorer par vous même ou l’adapter à vos besoins.

####################################################
# Title =                                          #
# transferDataSQLAzure.ps1                         #
# Description =                                    #
# Export datas from a database into files with bcp #
# Import datas from files into a database with bcp #
####################################################

# Parameters
# $action = in | out : in for import and out for export
# $srv = Server
# $login / $ pwd = Informations to connect to the server
# $src = file | path | ALL | ERASE :
#        file = file that contains datas to import into a database
#        path = path where the data files are present (all the files will be imported)
#        ALL = all tables from a database are exported ($action = in)
#        ERASE = all tables into a specified database are truncated
param([string]$action, [string]$srv, [string]$db, [string]$login, [string]$pwd, [string]$src) 

# Path parameters  – bcp data files
$scriptDir = Split-Path $MyInvocation.MyCommand.Path -Parent
$fileName = Split-Path $MyInvocation.MyCommand.Path -Leaf

# Path for bcp and sqlcmd tools
$sqlroot = « C:\Program Files\Microsoft SQL Server\100″
$bcp = « $sqlroot\Tools\binn\bcp.exe »
$sqlcmd = « $sqlroot\Tools\binn\sqlcmd.exe »

# Function Export-All
# Function saves all datas from a database into files with bcp
function Export-All
{
    # Load SQL Server SMO
    [reflection.assembly]::LoadWithPartialName(« Microsoft.SqlServer.Smo ») | out-null
    [reflection.assembly]::LoadWithPartialName(« Microsoft.SqlServer.ConnectionInfo ») | out-null

    # Connexion to the source database
    $mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $mySrvConn.ServerInstance=$srv
    $mySrvConn.LoginSecure = $false
    $mySrvConn.Login = $login
    $mySrvConn.Password = $pwd

    $srv_ = new-object Microsoft.SqlServer.Management.SMO.Server($mySrvConn)
    $smodb = $srv_.Databases[$db]

    # Export datas (one data file for one table)
    Write-Host « Export all tables from $db… » -Fore Cyan

    $smodb.Tables | % {
        Export $_.Schema $_.Name
    }
    Write-Host « All tables exported » -Fore Green
}

# Function Export
# Function export data from a table into a file
function Export($schm, $tbl)
{
    Write-Host « Export table [$db].[$schm].[$tbl] to [$schm].[$tbl].dat… » -Fore Cyan
    $args = « [$db].$schm.[$tbl] », « out », « [$schm].[$tbl].dat », « -S$srv », « -E », « -w », « -U$login », « -P$pwd »
    bcp $args
    Write-Host « Export table $db.dbo completed » -Fore Green
}

# Function Import
# Import all datas
function Import-All
{
    Write-Host « Importing data into all tables from $src… » -Fore Cyan
    (Get-ChildItem « $src\*.DAT ») | % {
        Import $_
    }
    Write-Host « All tables restored » -Fore Green
}

# Function Import
# Import data from a table into a file
function Import($datfile)
{
    if($datfile.Name)
    {
        $table = $datfile.Name.ToLower().Replace(« .dat », «  »)
        $file = $datfile.FullName
    }
    else
    {
        $file = (Split-Path $datfile -Leaf)
        $table = $file.ToLower().Replace(« .dat », «  »)
    }

    # Truncate table before importing datas
    Write-Host « Truncate table [$db].$table from $file… » -Fore Cyan
    & $sqlcmd -S $srv -U $login -P $pwd -d $db -Q ` »TRUNCATE TABLE $table` »

    Write-Host « Importing datas into the table $db.$table from $file… » -Fore Cyan
    $args = « $db.$table », « in », « $file », « -S$srv », « -U$login », « -P$pwd », « -w », « -E »
    & $bcp $args
    Write-Host « Importing completed » -Fore Green
}

# Function Erase-all
# Issue a truncate operation on all the tables
function Erase-All
{
    Write-Host « Truncate all tables from $src… » -Fore Cyan
    (Get-ChildItem « $src\*.DAT ») | % {
        Erase_table $_
    }
    Write-Host « All tables truncated » -Fore Green
}

# Function Erase_table
# Issue a truncate on the concerned table
function Erase_table($datfile)
{
    Write-Host « Truncate table $db.dbo.$table … » -Fore Cyan
    $table = $datfile.Name.ToLower().Replace(« .dat », «  »)
    & $sqlcmd -S $srv -U $login -P $pwd -d $db -Q ` »TRUNCATE TABLE $table` »Â 
    Write-Host « Truncate completed » -Fore Green
}

# Function Print-Help
# Print how to use the script
function Print-Help
{
    Write-Host « USAGE: » -Fore White
    Write-Host
    Write-Host  »Â  .\transferDataSQLAzure.ps1 out [servername database] [login] [password] [table | ALL | ERASE] » -Fore White
    Write-Host  »Â  Exports data from specified table to a file named after the table with a .DAT extension. » -Fore White
    Write-Host  »Â  If a table is only specified you have to provide the name with [schema].[table]  » -Fore White
    Write-Host  »Â  If ALL is specified instead of table name, all tables will be exported. » -Fore White
    Write-Host
    Write-Host  »Â  .\transferDataSQLAzure.ps1 in [servername] [database] [filename | path] [ERASE] » -Fore White
    Write-Host  »Â  Imports data from specified file to specified database. » -Fore White
    Write-Host  »Â  The file should have the same name as the table [schema].[table] with a .DAT extension. » -Fore White
    Write-Host  »Â  If a path is specified, ALL files will be imported. » -Fore White
    Write-Host
}

# Main program
function main
{
    # Export datas
    if($action -eq « out »)
    {
        if($src -eq « ALL »)
        {
            Export-All
        }
        else
        {
            Export $src
        }
    }
    # Import datas
    elseif($action -eq « in »)
    {
        Write-Host « WARNING!!! This will DELETE all data in the affected tables!!! ESC to abort. » -Fore Yellow
        $key = $host.ui.RawUI.ReadKey(« NoEcho,IncludeKeyUp,IncludeKeyDown »)
        if ($key.VirtualKeyCode -eq 27) # ESCAPE
        {
            return;
        }

        if((Test-Path $src -PathType Container))
        {
            Import-All
        }
        else
        {
            Import $src
        }
    }
    # Drop datas
    elseif($action -eq « ERASE »)
    {
        Erase-All
    }
    # Print Help
    else
    {
        Print-Help
    }
}

main

Bon chargement !!

David BARBARIN (Mikedavem)
MVP SQL Server