Export/import table par table sous SQL Server (bcp, sqlpackage)

Ce n'est pas une sauvegarde mais un transfert logique de données : export de tables avec bcp et chargement dans une autre base (format natif ou caractère, fichier de format, batch, TABLOCK, colonne d'identité, UTF-8), écriture du résultat d'une requête dans un fichier CSV/texte (queryout), transfert schéma + données avec sqlpackage (.bacpac Export/Import, par ex. migration vers Azure SQL Database) et schéma seul (.dacpac Extract, Script, Publish). Installation de mssql-tools18 et de sqlpackage sous Linux comprise. Vous obtenez sur la cible des tables dont le nombre de lignes est vérifié par rapport à la source, ou un fichier que vous pouvez transmettre.

Pour déplacer une base entière vers un SQL Server de même version ou plus récent, la sauvegarde/restauration est plus rapide et cohérente : Sauvegarde et restauration SQL Server.

Topologie: 1 serveur (les commandes s'exécutent sur le SQL Server lui-même ou sur un serveur d'administration)

Scénarios

• Déplacer des tables : bcp out → bcp in — Copier une ou plusieurs tables vers une autre base SQL Server ; les tables doivent déjà exister sur la cible.
• Écrire le résultat d'une requête dans un fichier : bcp queryout (CSV/texte) — Écrire le résultat d'une requête dans un fichier CSV/texte destiné à un autre système (Excel, ETL, reporting).
• Schéma + données : sqlpackage Export / Import (.bacpac) — Transférer schéma et données dans un seul fichier ; en particulier pour une migration vers Azure SQL Database ou pour des bases petites/moyennes.
• Schéma seul : sqlpackage Extract / Script / Publish (.dacpac) — Extraire uniquement le schéma, le comparer à la cible et générer un script de modification, ou déployer un schéma vide dans un nouvel environnement.

Avant de commencer

• Le bon outil a été choisi : - bcp (natif) : déplacer une ou plusieurs grosses tables de SQL Server à SQL Server ; la méthode la plus rapide. Les tables cibles doivent déjà exister. - bcp (caractère) / queryout : fournir du texte/CSV à d'autres systèmes (ETL, reporting, Excel). - .bacpac (Export/Import) : transférer schéma + données dans un seul fichier ; en particulier pour une migration vers Azure SQL Database. Lent sur les grosses bases (plusieurs centaines de Go) ; préférez alors la sauvegarde/restauration ou bcp. - .dacpac (Extract) : schéma seul (tables, vues, procédures) ; comparaison de schéma et déploiement d'un schéma vide dans un nouvel environnement.
• … dispose du droit de lecture sur la source (SELECT sur les tables ; db_owner recommandé pour Export/Extract) et du droit d'écriture sur la cible (INSERT + ALTER TABLE pour bcp in ; dbcreator ou db_owner pour Import/Publish). Fournissez le mot de passe avant le script : read -rsp 'Mot de passe : ' SQLCMDPASSWORD; export SQLCMDPASSWORD puis sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• PowerShell est ouvert en administrateur (Démarrer → « PowerShell » → clic droit → Run as administrator). …Le compte Windows qui a ouvert PowerShell…Le login …… dispose du droit de lecture sur la source et d'écriture sur la cible (INSERT + ALTER TABLE pour bcp in ; dbcreator ou db_owner pour Import/Publish).
• Les SQL Server source (…)… et cible (…)… sont joignables en TCP depuis le serveur de commandes.
• Les SQL Server source (…)… et cible (…)… sont joignables en TCP depuis le serveur de commandes ; bcp et sqlcmd sont dans le PATH (ils sont fournis avec l'installation de SQL Server ou avec « Microsoft Command Line Utilities for SQL Server »).
• Le disque qui contient le répertoire d'export (…) dispose d'autant d'espace libre que le volume de données à transférer (un fichier natif fait à peu près la taille des données de la table ; un fichier caractère peut être plus gros ; un .bacpac est compressé).
• Le lecteur qui contient le répertoire d'export (…) dispose d'autant d'espace libre que le volume de données à transférer.
• Les tables ont été créées dans la base cible (…) avec la même structure (bcp ne crée pas de tables). Le moyen simple de transférer le schéma est l'option Schéma seul (.dacpac) de ce guide. Pour les gros chargements, les index non cluster et les triggers de la table cible peuvent être désactivés, et les clés étrangères vérifiées ensuite.
• Aucune écriture n'a lieu sur la source pendant l'export (application arrêtée), ou une copie de la base (restaurée depuis une sauvegarde COPY_ONLY, ou un database snapshot) sert de source. bcp et sqlpackage Export ne sont pas cohérents au niveau transactionnel : des tables modifiées pendant l'export peuvent être incohérentes entre elles.

Phases du guide

• Outils et préparation — Installer les outils sqlcmd et bcp (mssql-tools18) · Installer sqlpackage · Vérifier les outils bcp et sqlcmd · Installer sqlpackage · Créer le répertoire d'export · Créer le répertoire d'export
• Transfert de tables (bcp out / in) — Relever le nombre de lignes et la taille des tables source · Générer les fichiers de format · Générer les fichiers de format · Exporter les tables (bcp out) · Exporter les tables (bcp out) · Vérifier les tables cibles …
• Export du résultat d'une requête (queryout) — Écrire le résultat de la requête dans un fichier (bcp queryout) · Écrire le résultat de la requête dans un fichier (bcp queryout)
• Schéma + données (.bacpac) — Exporter la base en .bacpac (Export) · Exporter la base en .bacpac (Export) · Créer la base cible (si elle n'existe pas) · Charger le .bacpac dans la cible (Import) · Charger le .bacpac dans la cible (Import) · Vérifier les tables et le nombre de lignes dans la base cible
• Schéma seul (.dacpac) — Extraire le schéma en .dacpac (Extract) · Extraire le schéma en .dacpac (Extract) · Créer la base cible (si elle n'existe pas) · Générer un script de modification par rapport à la cible (Script) · Générer un script de modification par rapport à la cible (Script) · Appliquer le schéma à la cible (Publish) …
• Ensuite — Nettoyer les fichiers d'export · Nettoyer les fichiers d'export