Procédure : mise à niveau d'une base de données avec Detach et Attach (Transact-SQL)

Mis à jour : 5 décembre 2005

Remarque relative à la sécurité :

Nous vous recommandons de ne pas attacher ou de restaurer des 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 provenant d'une source inconnue ou non approuvée, exécutez DBCC CHECKDB sur la base de données sur un serveur qui n'est pas un serveur de production et examinez le code (par exemple les procédures stockées ou le code défini par l'utilisateur) dans la base de données.

Dans SQL Server 2005, vous pouvez utiliser les opérations de détachement et d'attachement pour mettre à niveau une base de données utilisateur SQL Server version 7.0 ou SQL Server 2000. Cependant, les restrictions suivantes s'appliquent :

• Les sauvegardes de la base de données master, model ou msdb créées à l'aide de SQL Server 7.0 ou SQL Server 2000 ne peuvent pas être attachées dans SQL Server 2005.
• Les fichiers journaux de SQL Server 7.0 contenant des opérations de création d'index ne peuvent pas être attachés dans SQL Server 2000 ou SQL Server 2005.

Important :
Les sauvegardes de bases de données créées avec SQL Server version 6.5 ou antérieure sont dans un format incompatible et ne peuvent pas être attachées dans SQL Server 2005.

• 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).

Lors de l'utilisation des mots clés APPLY, PIVOT, TABLESAMPLE ou UNPIVOT sur des bases de données qui sont mises à niveau vers SQL Server 2005, le niveau de compatibilité de la base de données doit avoir la valeur 90. Pour définir le niveau de compatibilité de la base de données, consultez sp_dbcmptlevel (Transact-SQL).

Important :
Dans SQL Server 2000 Service Pack 3 (SP3) et versions ultérieures de SQL Server, attach et detach désactivent le chaînage de propriétés de bases de données croisées en affectant de la valeur 0 son option cross db ownership chaining. Pour des informations sur l'activation du chaînage, consultez Option cross db ownership chaining.

Procédures

Pour mettre à niveau une base de données avec detach et attach

1. Détachez la base de données de l'instance de SQL Server 7.0 ou SQL Server 2000 en utilisant la procédure stockée sp_detach_db.

   Pour plus d'informations, consultez la documentation en ligne de cette version de SQL Server.

   Remarque :
   Dans SQL Server 2005, cette procédure stockée a de nouvelles options. Pour plus d'informations, consultez sp_detach_db (Transact-SQL).

2. Facultativement, 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.

   Remarque :

   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 du nouveau fichier journal ou supprimez la copie d'origine du fichier journal (après l'avoir copiée au nouvel emplacement).

3. Attachez les fichiers copiés dans l'instance de SQL Server 2005 en utilisant l'instruction CREATE DATABASE avec l'option FOR ATTACH ou FOR ATTACH_REBUILD_LOG.

   Remarque :
   Pour plus d'informations sur l'attachement d'une base de données SQL Server 2005 à l'aide de l'Explorateur d'objets, consultez Procédure : attacher une base de données (SQL Server Management Studio).

4. Nous vous recommandons d'exécuter DBCC UPDATEUSAGE sur la base de données mise à niveau.

   Dans les versions antérieures de SQL Server, les valeurs du nombre de lignes de table et d'index et du nombre de pages peuvent être incorrectes. Par conséquent, les bases de données créées avec des versions antérieures à SQL Server 2005 peuvent contenir des décomptes incorrects. Après la mise à niveau d'une base de données vers SQL Server 2005, nous vous recommandons d'exécuter DBCC UPDATEUSAGE pour corriger d'éventuels compteurs incorrects. Cette instruction DBCC corrige les compteurs de lignes, de pages utilisées, de pages réservées, de pages de feuilles et de pages de données pour chaque partition de table ou d'index. Pour plus d'informations, consultez DBCC UPDATEUSAGE (Transact-SQL).

5. Facultativement, si vous effectuez une copie de la base de données (au lieu de la déplacer), vous pouvez rattacher la base de données d'origine sur l'instance de SQL Server 7.0 ou SQL Server 2000 en utilisant la procédure stockée sp_attach_db ou sp_attach_single_file_db.

   Pour plus d'informations, consultez la documentation en ligne de cette version de SQL Server.

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 Gestion des métadonnées lors de la mise à disposition d'une base de données sur une autre instance de serveur.

Exemple

Cet exemple met à niveau la base de données pubs SQL Server 2000 vers une base de données SQL Server 2005 en utilisant des instructions Transact-SQL pour détacher et attacher la base de données.

1. Connectez l'Analyseur de requêtes SQL Server 2000 à une instance de serveur où la base de données pubs est attachée, puis détachez-la en utilisant la procédure stockée sp_detach_db.

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

2. Pour cet exemple, en utilisant la méthode de votre choix, copiez les fichiers pubs (pubs.mdf et pubs_log.ldf) depuis C:\Program Files\Microsoft SQL Server\MSSQL\Data\ (emplacements par défaut de pubs dans SQL Server 2000) vers C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ (répertoire de données de SQL Server 2005).

   Important :
   Pour une base de données de production, placez la base de données et le journal des transactions sur des disques séparés.

   Remarque :

   Pour copier des fichiers par le réseau sur un disque installé sur un ordinateur distant en utilisant le nom UNC (universal naming convention) de l'emplacement distant. Un nom UNC prend la forme \\Servername\Sharename\Path\Filename. Comme lors de l'écriture de fichiers sur le disque dur local, le compte d'utilisateur qu'utilise SQL Server doit avoir reçu les autorisations nécessaires à la lecture ou à l'écriture des fichiers sur le disque distant.

3. Attachez la base de données pubs copiée et, en option, les fichiers journaux dans une instance de SQL Server 2005 (cet exemple utilise le même nom de base de données). Dans SQL Server Management Studio, ouvrez une nouvelle requête d'éditeur de requête et connectez-vous à l'instance de serveur à laquelle vous souhaitez attacher la base de données.

   Remarque :
   Pour plus d'informations, consultez Requête Transact-SQL - SQL Server Management Studio.

   Exécutez l'instruction CREATE DATABASE suivante.

   USE master;
   GO
   CREATE DATABASE pubs ON PRIMARY 
      (FILENAME = 
         'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
      LOG ON (FILENAME = 
         'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
      FOR ATTACH;
   GO
   

   Remarque :
   Dans SQL Server Management Studio, une base de données récemment attachée n'est pas immédiatement visible dans l'Explorateur d'objets. Pour afficher la base de données, cliquez sur la fenêtre Explorateur d'objets, puis sélectionnez Affichage > Actualiser. Lorsque le nœud Bases de données est développé, la base de données récemment attachée apparaît maintenant dans la liste des bases de données.

4. Éventuellement, rattachez la base de données d'origine pubs à l'instance de SQL Server 2000 en utilisant la procédure stockée sp_attach_db. Dans l'Analyseur de requêtes, entrez :

   USE master;
   Go
   EXEC sp_attach_db @dbname = N'pubs', 
      @filename1 = 
         N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
      @filename2 = 
         N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf';
   GO
   

Voir aussi

Tâches

Procédure : attacher une base de données (SQL Server Management Studio)
Procédure : effectuer une mise à niveau vers SQL Server 2005 à l'aide de l'Assistant Copie de base de données

Concepts

Attachement et détachement des bases de données
Sécurisation des fichiers de données et des fichiers journaux

Autres ressources

CREATE DATABASE (Transact-SQL)
Utilisation du Conseiller de mise à niveau pour la préparation des mises à niveau

Aide et Informations

Assistance sur SQL Server 2005