Mettre à niveau une base de données avec la méthode d’attachement et de détachement (Transact-SQL)

  • Article

S’applique à :SQL Server

Cette rubrique explique comment utiliser des opérations de détachement et d’attachement pour mettre à niveau une base de données dans SQL Server. Une fois attachée à SQL Server, la base de données est disponible immédiatement et est automatiquement mise à niveau. Cela empêche l’utilisation de la base de données avec une version antérieure du moteur de base de données. Toutefois, la mise à niveau des métadonnées n’affecte pas la définition du niveau de compatibilité de base de données d’une base de données. Pour plus d’informations, consultez Niveau de compatibilité des bases de données après une mise à niveau plus loin dans cette rubrique.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • Les bases de données système ne peuvent pas être attachées.

  • Attach et Detach désactivent le chaînage des propriétés des bases de données croisées pour la base de données en affectant la valeur 0 à l’option cross db ownership chaining . Pour plus d’informations sur l’activation du chaînage, consultez Chaînage des propriétés des bases de données croisées (option de configuration de serveur).

  • Si vous attachez une base de données répliquée qui a été copiée et non pas détachée :

    • Si vous attachez la base de données à une version mise à niveau de la même instance de serveur, vous devez exécuter sp_vupgrade_replication pour mettre à niveau la réplication au terme de l’opération d’attachement. Pour plus d’informations, consultez sp_vupgrade_replication (Transact-SQL).

    • Si vous attachez la base de données à une instance de serveur différente (quelle que soit sa version), vous devez exécuter sp_removedbreplication pour supprimer la réplication au terme de l’opération d’attachement. Pour plus d’informations, consultez sp_removedbreplication (Transact-SQL).

Recommandations

Nous vous recommandons de ne pas attacher ni restaurer de bases de données provenant de sources inconnues ou non approuvées. Ces bases de données peuvent contenir du code malveillant susceptible d'exécuter du code Transact-SQL indésirable ou de provoquer des erreurs en modifiant le schéma ou la structure physique des bases de données. Avant d’utiliser une base de données issue d’une source inconnue ou non approuvée, exécutez DBCC CHECKDB sur la base de données sur un serveur autre qu’un serveur de production et examinez également le code, notamment les procédures stockées ou le code défini par l’utilisateur, de la base de données.

Pour mettre à niveau une base de données à l'aide des opérations de détachement et d'attachement

  1. Détachez la base de données. Pour plus d’informations, consultez Détacher une base de données.

  2. Éventuellement, déplacez le ou les fichiers de base de données détachés et le ou les fichiers journaux.

    Vous devez déplacer les fichiers journaux ainsi que les fichiers de données, même si vous souhaitez créer de nouveaux fichiers journaux. Dans certains cas, le rattachement d'une base de données nécessite ses fichiers journaux existants. Par conséquent, conservez toujours tous les fichiers journaux détachés jusqu'à ce que la base de données ait été attachée avec succès sans eux.

    Note

    Si vous tentez d'attacher la base de données sans spécifier le fichier journal, l'opération attach recherche le fichier journal à son emplacement d'origine. Si la copie d'origine du journal figure toujours dans cet emplacement, la copie est attachée. Pour éviter d'utiliser le fichier journal d'origine, spécifiez le chemin d'accès au nouveau fichier journal ou supprimez la copie d'origine du fichier journal (après l'avoir copiée au nouvel emplacement).

  3. Joignez les fichiers copiés à l’instance de SQL Server. Pour plus d’informations, consultez Attach a Database.

Exemple

L'exemple suivant met à niveau une copie d'une base de données à partir d'une version antérieure de SQL Server. Les instructions Transact-SQL sont exécutées dans une fenêtre éditeur de requête connectée à l’instance de serveur à laquelle elle est attachée.

  1. Détachez la base de données en exécutant les instructions Transact-SQL suivantes :

    USE master;  
GO  
EXEC sp_detach_db @dbname = N'MyDatabase';  
GO

  2. À l'aide de la méthode de votre choix, copiez les fichiers de données et les fichiers journaux au nouvel emplacement.

    Important

    Dans le cas d’une base de données de production, placez de préférence la base de données et le journal des transactions sur des disques distincts. Comme ils ont des exigences différentes concernant les opérations d’E/S et la croissance des fichiers, nous vous conseillons de les maintenir séparés.

    Pour copier des fichiers via le réseau sur le disque d'un ordinateur distant, utilisez le nom UNC (Universal Naming Convention) de l'emplacement distant. Un nom UNC prend la forme \\Servername\Sharename\Path\Filename. Comme pour l’écriture de fichiers sur le disque dur local, les autorisations appropriées requises pour lire ou écrire dans un fichier sur le disque distant doivent être accordées au compte d’utilisateur utilisé par l’instance de SQL Server.

  3. Attachez la base de données déplacée et, éventuellement, son journal en exécutant l’instruction Transact-SQL suivante :

    USE master;  
GO  
CREATE DATABASE MyDatabase   
    ON (FILENAME = 'C:\MySQLServer\MyDatabase.mdf'),  
    (FILENAME = 'C:\MySQLServer\Database.ldf')  
    FOR ATTACH;  
GO

    Dans SQL Server Management Studio, une base de données nouvellement attachée n’est pas immédiatement visible dans l’Explorateur d’objets. Pour visualiser la base de données, dans l'Explorateur d'objets, cliquez sur Affichage puis sur Actualiser. Si le nœud Bases de données est développé dans l'Explorateur d'objets, la base de données récemment attachée apparaît dans la liste des bases de données.

Suivi : Après la mise à niveau d'une base de données SQL Server

Si la base de données comprend des index de recherche en texte intégral, la mise à niveau les importe, les réinitialise ou les reconstruit, selon le paramètre de la propriété de serveur upgrade_option . Si l’option de mise à niveau a la valeur Importer (upgrade_option = 2) ou Reconstruire (upgrade_option = 0), les index de recherche en texte intégral ne seront pas disponibles pendant la mise à niveau. Selon le volume de données indexé, l'importation peut prendre plusieurs heures et la reconstruction jusqu'à dix fois plus longtemps. Notez également que lorsque l'option de mise à niveau est Importer, les index de recherche en texte intégral associés sont reconstruits si aucun catalogue de texte intégral n'est disponible. Pour modifier le paramètre de la propriété de serveur upgrade_option , utilisez sp_fulltext_service.

Niveau de compatibilité des bases de données après une mise à niveau

Après la mise à niveau, le niveau de compatibilité des bases de données reste au niveau de compatibilité avant la mise à niveau, sauf si le niveau de compatibilité précédent n’est pas pris en charge dans la nouvelle version. Dans ce cas, le niveau de compatibilité des bases de données mises à niveau est défini sur le niveau de compatibilité le plus bas pris en charge.

Par exemple, si vous attachez une base de données au niveau de compatibilité 90 avant de l’attacher à une instance de SQL Server 2019 (15.x), après la mise à niveau, le niveau de compatibilité est défini sur 100, qui est le niveau de compatibilité le plus bas pris en charge dans SQL Server 2019 (15.x). Pour plus d’informations, consultez ALTER DATABASE - Niveau de compatibilité (Transact-SQL).

Gestion des métadonnées sur l'instance de serveur mise à niveau

Lorsque vous attachez une base de données à une autre instance de serveur et si vous souhaitez offrir une expérience cohérente aux utilisateurs et aux applications, il est possible que vous deviez recréer une partie ou l'ensemble des métadonnées de la base de données, telles que les connexions, les travaux, et les autorisations sur cette autre instance de serveur. Pour plus d’informations, consultez Gérer les métadonnées durant la mise à disposition d’une base de données sur une autre instance de serveur (SQL Server).

Modifications du chiffrement de la clé principale du service et de la clé principale de la base de données de 3DES à AES

SQL Server 2012 (11.x) et versions ultérieures utilisent l’algorithme de chiffrement AES pour protéger la clé principale du service (SMK) et la clé principale de base de données (DMK). AES est un algorithme de chiffrement plus récent que 3DES, qui était utilisé dans les versions antérieures. Lorsqu'une base de données est attachée ou restaurée pour la première fois à une nouvelle instance de SQL Server, une copie de la clé principale de la base de données (chiffrée par la clé principale du service) n'est pas encore stockée sur le serveur. Vous devez utiliser l'instruction OPEN MASTER KEY pour déchiffrer la clé principale de la base de données (DMK). Une fois la clé DMK déchiffrée, vous avez la possibilité d'activer le déchiffrement automatique dans le futur en exécutant l'instruction ALTER MASTER KEY REGENERATE pour fournir au serveur une copie de la clé DMK chiffrée avec la clé principale du service (SMK). Lorsqu'une base de données a été mise à niveau à partir d'une version antérieure, la clé DMK doit être régénérée de façon à utiliser le nouvel algorithme AES. Pour plus d’informations sur la régénération de la clé DMK, consultez ALTER MASTER KEY (Transact-SQL). La durée nécessaire pour régénérer la clé DMK à mettre à niveau vers AES dépend du nombre d'objets protégés par la clé DMK. La régénération de la clé DMK à mettre à niveau vers AES est nécessaire une seule fois et n'a aucune incidence sur les régénérations ultérieures effectuées dans le cadre d'une stratégie de rotation de clés.