1. Introduction et choix du format

Microsoft SQL Server propose plusieurs formats d'export selon l'usage cible :

2. Prerequis

2.1 Outils en ligne de commande

# Verifier que sqlcmd est installe
sqlcmd -?

# Verifier que bcp est installe
bcp -v

# Si absent : installer les "Microsoft Command Line Utilities for SQL Server"
# https://learn.microsoft.com/sql/tools/sqlcmd-utility
# ou via le pack "ODBC Driver" + "MSSQL Tools"

2.2 Lister les bases disponibles

# Authentification Windows (-E)
sqlcmd -S localhost -E -Q "SELECT name FROM sys.databases ORDER BY name"

# Avec instance nommee
sqlcmd -S localhost\SQLEXPRESS -E -Q "SELECT name FROM sys.databases"

# Authentification SQL Server
sqlcmd -S localhost -U sa -P MotDePasse -Q "SELECT name FROM sys.databases"

# Recuperer le nom du serveur / version
sqlcmd -S localhost -E -Q "SELECT @@SERVERNAME, @@VERSION"

2.3 Droits requis

• BACKUP DATABASE : role db_backupoperator ou sysadmin
• mssql-scripter : db_datareader + permissions sur les objets systeme (VIEW DEFINITION)
• bcp out : SELECT sur la table source

3. Export .bak (BACKUP DATABASE)

3.1 Backup complet (FULL)

# Authentification Windows
sqlcmd -S localhost -E -Q "BACKUP DATABASE [MaBase] TO DISK='D:\backup\MaBase_FULL.bak' WITH FORMAT, INIT, COMPRESSION, STATS=10"

# Authentification SQL
sqlcmd -S localhost -U sa -P MotDePasse -Q "BACKUP DATABASE [MaBase] TO DISK='D:\backup\MaBase_FULL.bak' WITH FORMAT, INIT, COMPRESSION"

Options principales

3.2 Backup avec date dans le nom

# PowerShell : insere la date dans le nom de fichier
$date = Get-Date -Format "yyyy-MM-dd_HHmm"
$base = "MaBase"
$dest = "D:\backup\${base}_${date}.bak"

sqlcmd -S localhost -E -Q "BACKUP DATABASE [$base] TO DISK='$dest' WITH FORMAT, INIT, COMPRESSION, STATS=10"

3.3 Backup differentiel

sqlcmd -S localhost -E -Q "BACKUP DATABASE [MaBase] TO DISK='D:\backup\MaBase_DIFF.bak' WITH DIFFERENTIAL, INIT, COMPRESSION"

3.4 Backup du journal des transactions

# Necessite recovery model FULL ou BULK_LOGGED
sqlcmd -S localhost -E -Q "BACKUP LOG [MaBase] TO DISK='D:\backup\MaBase_LOG.trn' WITH INIT, COMPRESSION"

3.5 Verifier l'integrite du .bak apres export

sqlcmd -S localhost -E -Q "RESTORE VERIFYONLY FROM DISK='D:\backup\MaBase_FULL.bak'"

# Lister le contenu du fichier (jeux de sauvegarde)
sqlcmd -S localhost -E -Q "RESTORE HEADERONLY FROM DISK='D:\backup\MaBase_FULL.bak'"

# Lister les fichiers logiques (MDF/LDF) contenus
sqlcmd -S localhost -E -Q "RESTORE FILELISTONLY FROM DISK='D:\backup\MaBase_FULL.bak'"

3.6 Backup vers plusieurs fichiers (parallelisation)

# Repartition sur 4 fichiers : plus rapide sur disques rapides
sqlcmd -S localhost -E -Q "BACKUP DATABASE [MaBase]
TO DISK='D:\backup\MaBase_1.bak',
   DISK='D:\backup\MaBase_2.bak',
   DISK='D:\backup\MaBase_3.bak',
   DISK='D:\backup\MaBase_4.bak'
WITH FORMAT, INIT, COMPRESSION, STATS=10"

4. Export .sql (mssql-scripter)

mssql-scripter est l'equivalent officiel de mysqldump pour MSSQL : il produit un fichier .sql en texte clair (schema, donnees, ou les deux).

4.1 Installation

# Necessite Python 3.x
pip install mssql-scripter

# Verifier
mssql-scripter --help

4.2 Export complet (schema + donnees)

# Authentification Windows
mssql-scripter -S localhost -d MaBase --integrated-auth --schema-and-data -f D:\backup\MaBase.sql

# Authentification SQL
mssql-scripter -S localhost -d MaBase -U sa -P MotDePasse --schema-and-data -f D:\backup\MaBase.sql

4.3 Schema uniquement (DDL)

mssql-scripter -S localhost -d MaBase --integrated-auth --schema-only -f D:\backup\MaBase_schema.sql

4.4 Donnees uniquement (INSERT)

mssql-scripter -S localhost -d MaBase --integrated-auth --data-only -f D:\backup\MaBase_data.sql

4.5 Options utiles

4.6 Export d'une table specifique avec donnees

mssql-scripter -S localhost -d MaBase --integrated-auth `
    --schema-and-data `
    --include-objects dbo.Clients dbo.Commandes `
    -f D:\backup\extrait.sql

5. Export CSV / TSV (bcp)

bcp (Bulk Copy Program) est l'utilitaire ultra-rapide pour exporter/importer des tables au format texte ou binaire.

5.1 Export d'une table en CSV

# Auth Windows (-T)
bcp MaBase.dbo.Clients out "D:\backup\clients.csv" -c -t"," -r"\n" -S localhost -T

# Auth SQL (-U / -P)
bcp MaBase.dbo.Clients out "D:\backup\clients.csv" -c -t"," -S localhost -U sa -P MotDePasse

5.2 Options bcp principales

5.3 Export via requete (queryout)

# Exporter le resultat d'une requete (pas une table entiere)
bcp "SELECT Id, Nom, Email FROM MaBase.dbo.Clients WHERE Actif=1" queryout "D:\backup\clients_actifs.csv" -c -t"," -S localhost -T

5.4 Export de toutes les tables (boucle PowerShell)

$server = "localhost"
$base   = "MaBase"
$dest   = "D:\backup\csv"
New-Item -ItemType Directory -Force -Path $dest | Out-Null

$tables = & sqlcmd -S $server -d $base -E -h-1 -W -Q "SET NOCOUNT ON; SELECT s.name + '.' + t.name FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id"

foreach ($t in $tables) {
    $t = $t.Trim()
    if ($t -and $t -notlike "*rows affected*") {
        $out = Join-Path $dest "$($t -replace '\.','_').csv"
        & bcp "$base.$t" out $out -c -t"," -S $server -T
    }
}

bcp MaBase.dbo.Clients out clients.csv -c -t"," -C 65001 -S localhost -T

6. Generate Scripts via SSMS (en ligne de commande)

SSMS (SQL Server Management Studio) propose un assistant graphique Tasks → Generate Scripts qui peut etre lance via la DLL Microsoft.SqlServer.Management.SmoExtended en PowerShell.

6.1 Export schema + data via SMO (PowerShell)

Import-Module SqlServer

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost"
$db  = $srv.Databases["MaBase"]

$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter $srv
$scripter.Options.ScriptSchema = $true
$scripter.Options.ScriptData   = $true
$scripter.Options.ScriptDrops  = $false
$scripter.Options.WithDependencies = $true
$scripter.Options.IncludeHeaders   = $true
$scripter.Options.FileName     = "D:\backup\MaBase_SMO.sql"
$scripter.Options.ToFileOnly   = $true
$scripter.Options.Encoding     = [System.Text.Encoding]::UTF8

# Lister toutes les tables
$tables = $db.Tables | Where-Object { -not $_.IsSystemObject }
$scripter.EnumScript($tables) | Out-Null

7. dacpac / bacpac (SqlPackage)

SqlPackage.exe est l'outil Microsoft pour exporter/importer des bases au format .bacpac (schema + data) ou .dacpac (schema seul). Format de choix pour Azure SQL et les migrations entre versions de SQL Server.

7.1 Installation

# SqlPackage est inclus dans :
# - SQL Server Data Tools (SSDT)
# - Azure Data Studio
# - Telechargement standalone : https://learn.microsoft.com/sql/tools/sqlpackage/sqlpackage-download

# Verifier
SqlPackage /?

7.2 Export .bacpac (schema + data)

SqlPackage /Action:Export `
    /SourceServerName:"localhost" `
    /SourceDatabaseName:"MaBase" `
    /TargetFile:"D:\backup\MaBase.bacpac" `
    /SourceTrustServerCertificate:True

7.3 Extract .dacpac (schema seul)

SqlPackage /Action:Extract `
    /SourceServerName:"localhost" `
    /SourceDatabaseName:"MaBase" `
    /TargetFile:"D:\backup\MaBase.dacpac"

8. Script PowerShell pret a l'emploi

Script complet, parametrable, avec rotation des sauvegardes (conserve les N derniers backups).

<#
.SYNOPSIS
    Export MSSQL .bak avec compression et rotation
.EXAMPLE
    .\backup-mssql.ps1 -Server "localhost" -Database "MaBase" -Destination "D:\backup" -Retention 7
#>
param(
    [string]$Server      = "localhost",
    [string]$Database    = "MaBase",
    [string]$Destination = "D:\backup",
    [int]   $Retention   = 7,
    [string]$User        = $null,
    [string]$Password    = $null
)

# Creer le dossier si besoin
if (-not (Test-Path $Destination)) {
    New-Item -ItemType Directory -Force -Path $Destination | Out-Null
}

# Nom du fichier
$date = Get-Date -Format "yyyy-MM-dd_HHmm"
$file = Join-Path $Destination "${Database}_${date}.bak"

# Construire la commande sqlcmd
$query = "BACKUP DATABASE [$Database] TO DISK='$file' WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS=10"

if ($User -and $Password) {
    $args = @("-S", $Server, "-U", $User, "-P", $Password, "-Q", $query)
} else {
    $args = @("-S", $Server, "-E", "-Q", $query)
}

Write-Host "[$(Get-Date -Format 'HH:mm:ss')] Backup de $Database vers $file..." -ForegroundColor Cyan
& sqlcmd $args

if ($LASTEXITCODE -eq 0) {
    $size = [math]::Round((Get-Item $file).Length / 1MB, 2)
    Write-Host "[OK] Backup termine ($size Mo)" -ForegroundColor Green

    # Rotation : supprimer les fichiers trop anciens
    Get-ChildItem $Destination -Filter "${Database}_*.bak" |
        Sort-Object LastWriteTime -Descending |
        Select-Object -Skip $Retention |
        ForEach-Object {
            Write-Host "  Suppression : $($_.Name)" -ForegroundColor Yellow
            Remove-Item $_.FullName -Force
        }
} else {
    Write-Host "[ERREUR] Backup en echec (code $LASTEXITCODE)" -ForegroundColor Red
    exit 1
}

9. Automatisation (Tache planifiee Windows)

9.1 Creer la tache via PowerShell

$action  = New-ScheduledTaskAction -Execute "powershell.exe" `
    -Argument "-NoProfile -ExecutionPolicy Bypass -File C:\scripts\backup-mssql.ps1 -Database MaBase -Destination D:\backup -Retention 7"

$trigger = New-ScheduledTaskTrigger -Daily -At 02:00

$principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest

Register-ScheduledTask -TaskName "MSSQL_Backup_MaBase" `
    -Action $action -Trigger $trigger -Principal $principal `
    -Description "Backup quotidien MSSQL"

9.2 Tester immediatement

Start-ScheduledTask -TaskName "MSSQL_Backup_MaBase"
Get-ScheduledTaskInfo -TaskName "MSSQL_Backup_MaBase"

9.3 Alternative : SQL Server Agent (Standard / Enterprise)

USE msdb;
EXEC sp_add_job @job_name = N'Backup_MaBase';
EXEC sp_add_jobstep
    @job_name = N'Backup_MaBase',
    @step_name = N'Backup full',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [MaBase] TO DISK=''D:\backup\MaBase.bak'' WITH INIT, COMPRESSION';
EXEC sp_add_schedule
    @schedule_name = N'Quotidien_02h',
    @freq_type = 4, @freq_interval = 1,
    @active_start_time = 020000;
EXEC sp_attach_schedule @job_name = N'Backup_MaBase', @schedule_name = N'Quotidien_02h';
EXEC sp_add_jobserver @job_name = N'Backup_MaBase';

10. Depannage courant