ALTER DATABASE (Transact-SQL)

Modifie une base de données, ou les fichiers et groupes de fichiers associés à la base de données. Ajoute ou supprime des fichiers et des groupes de fichiers d'une base de données, modifie ses attributs ou ses fichiers et groupes de fichiers, modifie le classement de la base de données et définit les options de la base de données. Les instantanés de base de données ne peuvent pas être modifiés. Pour modifier les options de base de données associées à la réplication, utilisez sp_replicationdboption.

S'applique à : SQL Server (SQL Server 2008 jusqu'à la version actuelle), Base de données SQL Azure.

En raison de sa longueur, la syntaxe d'ALTER DATABASE est divisée en plusieurs rubriques :

• ALTER DATABASE
  La rubrique actuelle fournit la syntaxe à utiliser pour renommer une base de données et en modifier le classement.

• Options de fichiers et de groupes de fichiers ALTER DATABASE
  Fournit la syntaxe à utiliser pour ajouter et supprimer des fichiers et groupes de fichiers d'une base de données, et pour modifier les attributs des fichiers et groupes de fichiers.

• Options ALTER DATABASE SET
  Fournit la syntaxe à utiliser pour modifier les attributs d'une base de données à l'aide des options SET d'ALTER DATABASE.

• Mise en miroir de bases de données ALTER DATABASE
  Fournit la syntaxe à utiliser pour les options SET d'ALTER DATABASE relatives à la mise en miroir de bases de données.

• ALTER DATABASE SET HADR
  Fournit la syntaxe des options Groupes de disponibilité AlwaysOn d'ALTER DATABASE en vue de la configuration d'une base de données secondaire sur un réplica secondaire d'un groupe de disponibilité AlwaysOn.

• Niveau de compatibilité ALTER DATABASE
  Fournit la syntaxe à utiliser pour les options SET d'ALTER DATABASE relatives aux niveaux de compatibilité de bases de données.

Conventions de la syntaxe Transact-SQL

Syntaxe

-- SQL Server Syntax
ALTER DATABASE { database_name  | CURRENT }
{
    MODIFY NAME = new_database_name 
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | <set_database_options>
}
[;]

<file_and_filegroup_options >::=
  <add_or_modify_files>::=
  <filespec>::= 
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::= 

<set_database_options>::=
  <optionspec>::= 
  <auto_option> ::= 
  <change_tracking_option> ::=
  <cursor_option> ::= 
  <database_mirroring_option> ::= 
  <date_correlation_optimization_option> ::=
  <db_encryption_option> ::=
  <db_state_option> ::=
  <db_update_option> ::=
  <db_user_access_option> ::= 
  <delayed_durability_option> ::=  <external_access_option> ::=
  <FILESTREAM_options> ::=
  <HADR_options> ::=  
  <parameterization_option> ::=
  <recovery_option> ::= 
  <service_broker_option> ::=
  <snapshot_option> ::=
  <sql_option> ::= 
  <termination> ::=

-- Azure SQL Database Syntax
ALTER DATABASE database_name 
{
    MODIFY NAME =new_database_name
  | MODIFY ( <edition_options> [, ... n] ) 
  | SET { <set_database_options> } 
}

<edition_options> ::= 
{
      MAXSIZE = { 100 MB | 500 MB |1 | 5 | 10 | 20 | 30 … 150 … 500 } GB  
    | EDITION = { 'web' | 'business' | 'basic' | 'standard' | 'Premium' } 
    | SERVICE_OBJECTIVE = { 'shared' | 'basic' | 'S0' | 'S1' | 'S2' | 'P1' | 'P2' | 'P3' } 
}

<set_database_options> ::= 
    <db_update_option>
<db_update_option> ::= 
    { READ_ONLY | READ_WRITE }
 [;]

Arguments

• database_name
  Nom de la base de données à modifier.

  Notes

  Cette option n'est pas disponible dans une base de données à relation contenant-contenu.

• CURRENT

  S'applique à : SQL Server 2012 jusqu'à SQL Server 2014.

  Indique que la base de données actuelle en cours d'utilisation doit être modifiée.

• MODIFY NAME **=**new_database_name
  Renomme la base de données avec le nom spécifié comme new_database_name.

• COLLATE collation_name

  S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

  Spécifie le classement par défaut de la base de données. collation_name peut être un nom de classement Windows ou un nom de classement SQL. S'il n'est pas spécifié, le classement par défaut de l'instance de SQL Server sera appliqué à la base de données.

  Pour plus d'informations sur les noms de classements Windows et SQL, consultez COLLATE (Transact-SQL).

• MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 5 | 10 | 20 | 30 … 150…500] Go)

  S'applique à : Base de données SQL Azure

  Spécifie la taille maximale de la base de données. La taille maximale doit être conforme au jeu de valeurs valide pour la propriété EDITION de la base de données. Le fait de modifier la taille maximale de la base de données peut entraîner la modification de la propriété EDITION de la base de données. Le tableau suivant contient la liste des valeurs de MAXSIZE prises en charge et des valeurs par défaut (D) des couches de service Base de données SQL.

  MAXSIZE	Web	Business	Basic	Standard	Premium
  100 Mo	√		√	√	√
  500 Mo			√	√	√
  1 Go	√ (D)		√	√	√
  2 Go			√ (D)	√	√
  5 Go	√			√	√
  10 Go		√ (D)		√	√
  20 Go		√		√	√
  30 Go		√		√	√
  40 Go		√		√	√
  50 Go		√		√	√
  100 Go		√		√	√
  150 Go		√		√	√
  200 Go				√	√
  250 Go				√ (D)	√
  300 Go					√
  400 Go					√
  500 Go					√ (D)

  Les règles suivantes s'appliquent aux arguments MAXSIZE et EDITION.

  • La valeur MAXSIZE, si spécifiée, doit être une valeur valide indiquée dans le tableau ci-dessus.

  • Si MAXSIZE est inférieur à 5 Go et que EDITION n'est pas spécifié, l'édition de la base de données sera automatiquement définie à « Web ».

  • Si MAXSIZE est supérieur à 5 Go et que EDITION n'est pas spécifié, l'édition de la base de données sera automatiquement « Business ».

  • Si EDITION est spécifié, mais MAXSIZE n'est pas spécifié, la valeur par défaut de l'édition est utilisée. Par exemple, si EDITION est défini à Standard et que MAXSIZE n'est pas spécifié, MAXSIZE est automatiquement défini à 500 Mo.

  • Si MAXSIZE et EDITION ne sont pas spécifiés, EDITION est défini à « Web » et MAXSIZE est défini à 1 Go.

• MODIFY (EDITION = [ 'web' | 'business' | 'basic' | 'standard' | 'premium' ] )

  S'applique à : Base de données SQL Azure

  Modifie l'édition de la base de données. Les couches de service Base de données SQL peuvent être définies ou modifiées à l'aide du paramètre EDITION. La modification de EDITION échoue si la propriété MAXSIZE de la base de données a une valeur située hors de la plage valide prise en charge par cette édition.

  Important

  Les niveaux de service « Business » et « Web » seront retirés en septembre 2015.Pour plus d'informations, consultez Questions fréquentes sur Web et Business.

• SERVICE_OBJECTIVE

  S'applique à : Base de données SQL Azure

  Spécifie le niveau de performances. Pour plus d'informations sur les objectifs de service, ainsi que sur la taille, les éditions et les combinaisons d'objectifs de service, consultez Niveaux de service et niveaux de performances des bases de données SQL Azure. Si le SERVICE_OBJECTIVE spécifié n'est pas pris en charge par l'EDITION, un message d'erreur s'affiche. Si vous voulez modifier la valeur de SERVICE_OBJECTIVE pour passer d'un niveau de service à un autre (par exemple de S1 à P1), vous devrez également modifier la valeur d'EDITION.

• <db_update_option> ::=

  S'applique à : Base de données SQL Azure

  Contrôle si des mises à jour sont autorisées dans la base de données.

  { READ_ONLY | READ_WRITE }

  • READ_ONLY
    Les utilisateurs peuvent lire des données dans la base de données mais ils n'ont pas le droit de les modifier.

  • READ_WRITE
    La base de données est accessible aux opérations de lecture et d'écriture.

  Notes

  Dans les bases de données fédérées Base de données SQL, SET {READ_ONLY | READ_WRITE} est désactivé.

<delayed_durability_option> ::=

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Pour plus d'informations, consultez Options SET d'ALTER DATABASE (Transact-SQL) et Contrôler la durabilité d'une transaction.

<file_and_filegroup_options >::=

Pour plus d'informations, consultez Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL).

<set_database_options >::=

Pour plus d'informations, consultez Options SET d'ALTER DATABASE (Transact-SQL), Mise en miroir de bases de données ALTER DATABASE (Transact-SQL), ALTER DATABASE SET HADR (Transact-SQL) et Niveau de compatibilité ALTER DATABASE (Transact-SQL).

Notes

Pour supprimer une base de données, utilisez DROP DATABASE.

Pour diminuer la taille d'une base de données, utilisez DBCC SHRINKDATABASE.

L'instruction ALTER DATABASE doit être exécutée en mode de validation automatique (mode de gestion des transactions par défaut) et n'est pas autorisée dans une transaction explicite ou implicite.

L'état d'un fichier de base de données (par exemple, en ligne ou hors connexion) est préservé indépendamment de l'état de la base de données. Pour plus d'informations, consultez États des fichiers. L'état des fichiers dans un groupe de fichiers détermine la disponibilité de tout le groupe de fichiers. Pour qu'un groupe de fichiers soit disponible, tous ses fichiers doivent être en ligne. Si un groupe de fichiers est hors connexion, toute tentative d'accès au groupe par une instruction SQL échoue avec une erreur. Lorsque vous créez des plans de requête pour les instructions SELECT, l'optimiseur de requête évite les index non cluster et les vues indexées qui résident dans les groupes de fichiers hors connexion. Cela permet aux instructions de s'exécuter correctement. Cependant, si le groupe de fichiers hors connexion contient le segment ou l'index cluster d'une table cible, les instructions SELECT échouent. De plus, toute instruction INSERT, UPDATE ou DELETE modifiant une table assortie d'un index dans un groupe de fichiers hors connexion ne peut être exécutée.

Lorsque l'état d'une base de données est RESTORING, les instructions ALTER DATABASE, pour la plupart, échouent. La définition des options de mise en miroir de bases de données fait exception. Une base de données peut être à l'état RESTORING durant une opération de restauration active, ou lorsqu'une opération de restauration d'un fichier de base de données ou d'un fichier journal échoue car un fichier de sauvegarde est corrompu.

Le cache du plan pour l'instance de SQL Server est effacé par la définition de l'une des options suivantes :

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY	PAGE_VERIFY

Cette opération entraîne la recompilation de tous les plans d'exécution ultérieurs et peut entraîner une baisse temporaire et brutale des performances des requêtes. Pour chaque mémoire cache effacée dans le cache de plan, le journal des erreurs SQL Server contient le message d'information suivant : "SQL Server a rencontré %d occurrence(s) de vidage de mémoire cache pour la mémoire cache '%s' (partie du cache du plan) en raison d'opérations de maintenance ou de reconfiguration." Ce message est enregistré toutes les cinq minutes si le cache est vidé au cours de cet intervalle de temps.

Le cache de procédures est également vidé dans les scénarios suivants :

• L'option de base de données AUTO_CLOSE est activée (ON). Lorsqu'aucune connexion utilisateur ne fait référence ou n'utilise la base de données, la tâche en arrière-plan essaie de fermer et d'arrêter la base de données automatiquement.

• Vous exécutez plusieurs requêtes sur une base de données dont les options par défaut sont activées. Puis, la base de données est supprimée.

• Un instantané de base de données pour une base de données source est supprimé.

• Vous reconstruisez avec succès le journal des transactions d'une base de données.

• Vous restaurez une sauvegarde de base de données.

• Vous détachez une base de données.

Modification du classement de la base de données

Avant d'appliquer un autre classement à une base de données, veillez à ce que les conditions suivantes soient remplies :

1. Vous êtes actuellement le seul à utiliser la base de données.

2. Aucun objet lié à un schéma ne dépend du classement de la base de données.

   Si les objets suivants, qui dépendent du classement de base de données, existent dans la base de données, l'instruction ALTER DATABASE database_name COLLATE échoue. SQL Server retourne un message d'erreur pour chaque objet bloquant l'action ALTER :

   • Fonctions et vues définies par l'utilisateur créées avec SCHEMABINDING

   • Colonnes calculées

   • Contraintes CHECK

   • Fonctions table qui retournent des tables comportant des colonnes de type caractère avec des classements hérités du classement par défaut de la base de données

   Les informations de dépendance des entités non liées au schéma sont mises à jour automatiquement lorsque le classement de la base de données est modifié.

La modification du classement de la base de données ne crée pas de doublons parmi les noms système des objets de la base de données. Si cette modification entraîne la duplication de noms, les espaces de noms suivants peuvent faire échouer une modification du classement de la base de données :

• Noms d'objets tels qu'une procédure, une table, un déclencheur ou une vue

• Noms de schémas.

• Principaux, tels qu'un groupe, un rôle ou un utilisateur

• Noms de types scalaires, comme les types système ou définis par l'utilisateur

• Noms de catalogues de texte intégral

• Noms de colonnes ou de paramètres dans un objet

• Noms d'index dans une table

Les noms en double qui résultent du nouveau classement entraînent l'échec de l'action de modification et SQL Server retourne un message d'erreur spécifiant l'espace de noms en cause.

Affichage des informations de bases de données

Vous pouvez utiliser les affichages catalogue, les fonctions système et les procédures stockées du système pour retourner des informations sur les bases de données, les fichiers et les groupes de fichiers.

Autorisations

SQL Server

Nécessite l'autorisation ALTER sur la base de données.

Base de données SQL Azure

Seule la connexion principale au niveau du serveur (créée par le processus de configuration) ou les membres du rôle de base de données dbmanager peuvent modifier une base de données.

Remarque relative à la sécurité
Le propriétaire de la base de données ne peut pas modifier la base de données à moins d'être membre du rôle dbmanager.

Exemples

A.Modification du nom d'une base de données

L'exemple suivant modifie le nom de la base de données AdventureWorks2012 en Northwind.

USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

B.Modification du classement d'une base de données

L'exemple suivant crée une base de données nommée testdb qui utilise le classement SQL_Latin1_General_CP1_CI_AS, puis modifie le classement de la base de données testdb en COLLATE French_CI_AI.

S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

Voir aussi

Référence

CREATE DATABASE (SQL Server Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

EVENTDATA (Transact-SQL)

sp_configure (Transact-SQL)

sp_spaceused (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

sys.database_mirroring_witnesses (Transact-SQL)

sys.data_spaces (Transact-SQL)

sys.filegroups (Transact-SQL)

sys.master_files (Transact-SQL)

Concepts

Bases de données système