Comment utiliser DBCC CLONEDATABASE pour générer un schéma et des statistiques uniquement copie d'une base de données utilisateur dans SQL Server

S’applique à : SQL Server 2014 Business IntelligenceSQL Server 2014 DeveloperSQL Server 2014 Enterprise

Résumé


Cette mise à jour introduit une nouvelle commande de gestion DBCC CLONEDATABASE (Transact-SQL) dans SQL Server 2017 sur Windows, Service Pack 4 pour SQL Server 2012, Microsoft SQL Server 2014 Service Pack 2 et SQL Server 2016 SP1. Cette commande crée une nouvelle base de données qui contient le schéma de tous les objets et statistiques de la base de données source spécifiée.
Remarque Dans les environnements SQL Server 2014, vous devez installer la mise à jour cumulative 3 pour SQL Server 2014 SP2 afin d'obtenir la possibilité de cloner des objets Filestream, FileTable et Language runtime (CLR) et de cloner avec les options NO-STATISTICS en utilisant DBCC.
 
À propos des packs de service pour SQL Server

À propos de DBCC CLONEDATABASE


Microsoft Customer Support Services peut vous demander de générer un clone d'une base de données en utilisant DBCC CLONEDATABASE afin d'enquêter sur un problème de performances liés à l'optimiseur de requêtes. Remarque La base de données nouvellement générée par DBCC CLONEDATABASE n'est pas prise en charge pour être utilisée comme base de données de production et est principalement destinée à des fins de dépannage et de diagnostic. Nous vous recommandons de détacher la base de données clonée après la création de la base de données. Le clonage de la base de données source est effectué par les opérations suivantes :
  • Crée une nouvelle base de données de destination qui utilise la même disposition de fichier que la source, mais avec des tailles de fichiers par défaut que la base de données du modèle.
  • Crée un instantané interne de la base de données source.
  • Copie les métadonnées du système de la source à la base de données de destination.
  • Copie tous les schémas pour tous les objets de la source à la base de données de destination.
  • Copie des statistiques de tous les index de la source à la base de données de destination.

syntaxe


DBCC CLONEDATABASE (source_database_name, target_database_name)[WITH [NO_STATISTICS][,NO_QUERYSTORE]] 

Arguments


  • source-database-nom Cet argument est le nom de la base de données dont le schéma et les statistiques doivent être copiés.
  • target-database-nom Cet argument est le nom de la base de données à laquelle le schéma et les statistiques de la base de données source seront copiés. Cette base de données sera créée par DBCC CLONEDATABASE et ne devrait pas exister déjà.
  • NON-STATISTIQUES Cet argument spécifie si les statistiques de tableau/index doivent être exclues dans le clone. Si cette option n'est pas spécifiée, les statistiques de tableau/index sont automatiquement incluses. Cette option est disponible à partir de SQL Server 2014 SP2 CU3 et SQL Server 2016 Service Pack 1.
  • NO-QUERYSTORE (EN ANGLAIS SEULEMENT) Cet argument spécifie si le magasin de requêtes doit être exclu dans le clone. Si cette option n'est pas spécifiée, les données du magasin de requêtes sont copiées sur le clone si elles sont activées dans la base de données source. Cette option est disponible à partir de SQL Server 2016 Service Pack 1.

Quand utiliser DBCC CLONEDATABASE ?


DBCC CLONEDATABASE doit être utilisé pour créer un schéma et des statistiques uniquement copier d'une base de données de production afin d'enquêter sur les problèmes de performances de requête. Soyez conscient des restrictions et des objets pris en charge suivants :
  • Restrictions Les validations suivantes sont effectuées par DBCC CLONEDATABASE. La commande échoue si l'une des validations échoue.
    • La base de données source doit être une base de données utilisateur. Le clonage des bases de données système (maître, modèle, msdb, tempdb, base de données de distribution, etc.) n'est pas autorisé.
    • La base de données source doit être en ligne ou lisible.
    • Une base de données qui utilise le même nom que la base de données clone ne doit pas déjà exister.
    • La commande n'est pas dans une transaction utilisateur.
    Si toutes les validations réussissent, DBCC CLONEDATABASE exécutera les opérations suivantes :
    • Création de fichier de données primaires et de fichier journal
    • Ajout d'espaces de données secondaires
    • Ajout de fichiers secondaires
    Remarque Tous les fichiers de la base de données cible hériteront des paramètres de taille et de croissance de la base de données du modèle. Convention sur les noms de fichiers : Les noms de fichiers de la base de données de destination suivront la convention de numéros de fichiers source-fichier -underscore-random. Si le nom de fichier généré existe déjà dans le dossier de destination, DBCC CLONEDATABASE échouera.
  • Instantané de basede données interneDBCC CLONEDATABASE utilise un instantané de base de données interne de la base de données source pour la cohérence transactionnelle qui est nécessaire pour effectuer la copie. Cela empêche les problèmes de blocage et de concurrence lorsque ces commandes sont exécutées. Si un instantané ne peut pas être créé, DBCC CLONEDATABASE échouera. Les verrous de niveau de base de données sont tenus pendant les étapes suivantes du processus de copie :
    • Valider la base de données source
    • Obtenir le verrou S pour la base de données source
    • Créer un instantané de la base de données source
    • Créer une base de données de clones (il s'agit d'une base de données vide qui hérite du modèle)
    • Obtenir le verrou X pour la base de données de clones
    • Copier les métadonnées dans la base de données clone
    • Relâchez tous les verrous DB
    Dès que la commande a terminé l'exécution, l'instantané interne est supprimé. Les options TRUSTWORTHY et DB-CHAINING sont désactivées sur une base de données clonée.

Objets pris en charge


Seul le schéma d'objets suivants sera cloné dans la base de données de destination. Les objets cryptés sont clonés mais ne sont pas pris en charge en clone dans cette version de SQL Server. Tous les objets qui ne sont pas répertoriés dans la section suivante ne sont pas pris en charge dans le cloné :

  • RÔLE D'APPLICATION
  • GROUPE AVAILABILITY
  • INDICE COLUMNSTORE
  • CDB (En)
  • Cdc
  • Changement tracking (SQL Server 2016 SP2 CU10, SQL Server 2017 CU17, SQL Server 2019 CU1 et versions ultérieures)
  • CLR (démarrage de SQL Server 2014 SP2 CU3, SQL Server 2016 Service Pack 1 et versions ultérieures)
  • PROPRIÉTÉS DE BASE DE DONNÉES
  • manquer à ses engagements
  • FICHIERS ET GROUPES DE FICHIERS
  • Texte intégral (démarrage DE SQL Server 2016 SP1 CU2)
  • fonction
  • INDEX
  • connectez-vous
  • FONCTION DE PARTITION
  • SCHÉMA DE PARTITION
  • procédure Remarque Les procédures T-SQL sont prises en charge dans toutes les versions commençant SQL Server 2014 SP2. Les procédures CLR sont prises en charge à partir de SQL Server 2014 SP2 CU3. Les procédures compilées de façon native sont prises en charge à partir de SQL Server 2016 SP1.
  • QUERY STORE (seulement dans SQL Server 2016 Service Pack 1 et versions ultérieures) Remarque Query Store n'est copié que s'il est activé dans la base de données source. Pour copier les dernières statistiques d'exécution dans le cadre de Query Store, exécutez sp-query-store-flush-db pour rincer les statistiques d'exécution dans le magasin de requêtes avant d'exécuter DBCC CLONEDATABASE.
  • rôle
  • règle
  • Schéma
  • ordre
  • INDICE SPATIAL
  • statistiques
  • synonyme
  • table
  • MEMORY OPTIMIZED TABLES (seulement dans SQL Server 2016 SP1 et versions ultérieures).
  • FILESTREAM ET FILETABLE OBJECTS (Starting SQL Server 2014 SP2 CU3, SQL Server 2016 SP1 et les versions ultérieures).
  • déclencher
  • type
  • DB UPGRADED
  • utilisateur
  • Vue
  • INDICE XML
  • COLLECTION XML SCHEMA

 

Autorisations


Vous devez avoir l'adhésion dans le rôle de serveur fixe sysadmin.

Limitations et considérations


DBCC CLONEDATABASE ne prend pas en charge la création d'un clone s'il y a des objets utilisateur (tables, index, schémas, rôles, etc.) qui ont été créés dans la base de données du modèle. Si des objets utilisateur sont présents dans la base de données du modèle, le clone de base de données échoue avec le message d'erreur suivant :

Msg 2601, Niveau 14, État 1, Ligne 1Ne peut pas insérer la ligne de clé en double dans l'objet'lt;system table'gt; avec un index unique 'nom de l'index'. La valeur clé en double est la valeur de la clé de lt;key .'

Pour plus d'informations relatives à la sécurité des données sur les bases de données clonées, voir le blog suivant: Comprendre la sécurité des données dans lesbases de données clonées.

Si vous avez des index de magasin de colonnes, consultez le blog suivant :Considérations lorsque vousaccordez les requêtes avec les index Columnstore sur des bases de données de clones pour mettre à jour les statistiques d'index de columnstore avant d'exécuter la commande CloneDATABASE de DBCC .

Messages de journal d'erreur


Les messages suivants sont enregistrés dans le journal d'erreur pendant le processus de clonage :
Le clonagede base de données de 'sourcedb' a commencé avec la cible en tant que 'sourcedb'clone'. TRUSTWORTHY à OFF pour la base de données 'sourcedb'clone'.La base de données 'sourcedb'clone' est une base de données clonée. Une base de données clonée doit être utilisée à des fins diagnostiquesseulement et n'est pas pris en charge pour une utilisation dans un environnement de production. La base de données clonée est 'sourcedb-clone'.

Propriété de base de données


Une nouvelle base de données isClone est ajoutée. DATABASEPROPERTYEX ('dbname', 'IsClone') retournera 1 si la base de données est générée en utilisant DBCC CLONEDATABASE.

Exemples


  1. Création d'un clone de la base de données AdventureWorks qui inclut les schémas, les statistiques et le magasin de requêtes (SQL Server 2016 SP1 et versions ultérieures)
    Transact-SQL -- Generate the clone of AdventureWorks database.    DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone);    GO 
  2. Création d'un clone schéma-seulement de la base de données AdventureWorks dans SQL Server 2014 sans statistiques (SQL Server 2014 SP2 CU3 et versions ultérieures)
    DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH NO_STATISTICS
  3. Création d'un clone schéma-seulement de la base de données AdventureWorks sans statistiques et magasin de requêtes (SQL Server 2016 SP1 et versions ultérieures)
    DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH NO_STATISTICS,NO_QUERYSTORE

Références


Découvrez la terminologie utilisée par Microsoft pour décrire les mises à jour logicielles.