• Articolo

Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)

Modifica i file e i filegroup associati al database. Consente inoltre di aggiungere o rimuovere file e filegroup da un database, di modificare gli attributi di un database oppure i relativi file e filegroup. Per altre opzioni relative ad ALTER DATABASE, vedere ALTER DATABASE (Transact-SQL).

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = {'os_file_name' | 'filestream_path' } ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
) 

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
        [ CONTAINS FILESTREAM ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

Argomenti

<add_or_modify_files>::=

Specifica i file da aggiungere, rimuovere o modificare.

  • database_name
    Nome del database da modificare.

  • ADD FILE
    Aggiunge un file al database.

    • TO FILEGROUP { filegroup_name }
      Specifica il filegroup in cui aggiungere il file specificato. Per visualizzare i filegroup correnti e determinare il filegroup attualmente predefinito, utilizzare la vista del catalogo sys.filegroups.

  • ADD LOG FILE
    Aggiunge un file di log al database specificato.

  • REMOVE FILE logical_file_name
    Rimuove la descrizione del file logico da un'istanza di SQL Server ed elimina il file fisico. Il file può essere rimosso solo se è vuoto.

    • logical_file_name
      Nome logico utilizzato in SQL Server per fare riferimento al file.

  • MODIFY FILE
    Specifica il file da modificare. È possibile modificare una sola proprietà <filespec> alla volta. L'opzione NAME deve essere sempre specificata in <filespec> per identificare il file da modificare. Se si specifica l'opzione SIZE, le nuove dimensioni del file devono essere superiori a quelle correnti.

    Per modificare il nome logico di un file di dati o di un file di log, specificare il nome del file logico da rinominare nella clausola NAME e specificare il nuovo nome logico per il file nella clausola NEWNAME. Ad esempio:

    MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

    Per spostare un file di dati o un file di log in una nuova posizione, specificare il nome di file logico corrente nella clausola NAME e specificare il nuovo percorso e il nome del file nel sistema operativo nella clausola FILENAME. Ad esempio:

    MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

    Per lo spostamento di un catalogo full-text, specificare solo il nuovo percorso nella clausola FILENAME, senza indicare il nome del file nel sistema operativo.

    Per ulteriori informazioni, vedere Spostamento dei file del database.

    Per un filegroup FILESTREAM, NAME può essere modificato in linea. Sebbene FILENAME possa essere modificato in linea, la modifica non diventa effettiva fino a quando il contenitore non viene rilocato fisicamente e il server non viene arrestato e successivamente riavviato.

    È possibile impostare un file FILESTREAM su OFFLINE. Quando un file FILESTREAM non è in linea, il filegroup padre sarà contrassegnato internamente come non in linea. Di conseguenza ogni accesso a dati FILESTREAM all'interno del filegroup specifico avrà esito negativo.

<filespec>::=

Controlla le proprietà del file.

  • NAME logical_file_name
    Specifica il nome logico del file.

    • logical_file_name
      Nome logico utilizzato in un'istanza di SQL Server per fare riferimento al file.

  • NEWNAME new_logical_file_name
    Specifica un nuovo nome logico per il file.

    • new_logical_file_name
      Nome con cui sostituire il nome di file logico esistente. Il nome deve essere univoco all'interno del database e conforme alle regole per gli identificatori. Il nome può essere costituito da una costante per valori di carattere o Unicode, da un identificatore regolare o da un identificatore delimitato. Per ulteriori informazioni, vedere Utilizzo degli identificatori come nomi di oggetti.

  • FILENAME { 'os_file_name' | 'filestream_path' }
    Specifica il nome del file (fisico) del sistema operativo.

    • ' os_file_name '
      Per un filegroup standard (ROWS) , questo è il percorso e il nome di file utilizzato dal sistema operativo al momento della creazione del file. Il file deve trovarsi nel server in cui è installato SQL Server. Il percorso specificato deve essere esistente prima di eseguire l'istruzione ALTER DATABASE.

      Non è consentita l'impostazione dei parametri SIZE, MAXSIZE e FILEGROWTH se si specifica un percorso UNC per il file.

      I file di dati non dovrebbero essere memorizzati in file system compressi, a meno che tali file non siano file secondari di sola lettura o il database non sia di sola lettura. I file di log non devono mai essere posizionati in file system compressi. Per ulteriori informazioni, vedere Filegroup di sola lettura e compressione.

      Se il file si trova in una partizione non formattata dal sistema operativo, nell'argomento os_file_name è necessario specificare solo la lettera dell'unità di una partizione non formattata esistente. In ogni partizione non formattata è possibile inserire un solo file.

    • 'filestream_path'
      Per un filegroup FILESTREAM, FILENAME si riferisce a un percorso in cui verrà archiviato FILESTREAM. È necessario che il percorso fino all'ultima cartella esista già, mentre l'ultima cartella non deve essere presente. Se, ad esempio, si specifica il percorso C:\MyFiles\MyFilestreamData, C:\MyFiles deve esistere già prima di eseguire ALTER DATABASE, mentre la cartella MyFilestreamData non deve essere presente.

      Il filegroup e il file (<filespec>) devono essere creati nella stessa istruzione. Può essere presente un solo file, <filespec>, per un filegroup FILESTREAM.

      Le proprietà SIZE, MAXSIZE e FILEGROWTH non si applicano a un filegroup FILESTREAM.

  • SIZE size
    Specifica le dimensioni del file. SIZE non si applica a filegroup FILESTREAM.

    • size
      Dimensioni del file.

      Quando viene specificato con ADD FILE, size corrisponde alle dimensioni iniziali del file. Se specificato con MODIFY FILE, size corrisponde alle nuove dimensioni del file, che devono essere superiori a quelle correnti.

      Se non si specifica il parametro size per il file primario, SQL Server utilizza le dimensioni del file primario nel database modello. Se si specifica un file di dati o di log secondario senza impostare il parametro size per il file, Motore di database assegna dimensioni di 1 MB al file.

      È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se si desidera specificare una frazione di megabyte, convertire il valore in kilobyte moltiplicando il numero per 1024. Ad esempio, specificare 1536 KB anziché 1,5 MB (1,5 x 1024 = 1536).

  • MAXSIZE { max_size| UNLIMITED }
    Specifica le dimensioni massime consentite per l'aumento di dimensioni del file. MAXSIZE non si applica a filegroup FILESTREAM.

    • max_size
      Dimensioni massime del file. È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se non si specifica max_size, le dimensioni del file aumenteranno fino a quando il disco risulta pieno.

    • UNLIMITED
      Specifica che le dimensioni del file aumentano fino a quando il disco risulta pieno. In SQL Server un file di log specificato con aumento delle dimensioni illimitato può raggiungere una dimensione massima di 2 TB, mentre un file di dati può raggiungere una dimensione massima di 16 TB.

  • FILEGROWTH growth_increment
    Specifica l'incremento automatico per l'aumento delle dimensioni del file. Il valore impostato per il parametro FILEGROWTH di un file non può essere superiore al valore del parametro MAXSIZE. FILEGROWTH non si applica a filegroup FILESTREAM.

    • growth_increment
      Quantità di spazio aggiunta al file ogni volta che è necessario spazio aggiuntivo.

      È possibile specificare il valore in megabyte (MB), kilobyte (KB), gigabyte (GB) o terabyte (TB) oppure in forma di percentuale (%). Se si specifica un valore senza il suffisso MB, KB o %, il suffisso predefinito è MB. Se si utilizza il suffisso %, l'incremento corrisponde alla percentuale delle dimensioni del file specificata quando si verifica l'incremento. Le dimensioni specificate vengono arrotondate al blocco di 64 KB più prossimo.

      Il valore 0 indica che l'aumento automatico delle dimensioni è disattivato e non è consentita l'allocazione di spazio aggiuntivo.

      Se FILEGROWTH viene omesso, il valore predefinito è 1 MB per i file di dati e 10% per i file di log e il valore minimo è 64 KB.

      [!NOTA]

      A partire da SQL Server 2005 l'incremento predefinito per l'aumento delle dimensioni del file è passato dal 10% a 1 MB. L'impostazione predefinita del 10% per i file di log è rimasta invariata.

  • OFFLINE
    Imposta il file non in linea e rende inaccessibili tutti gli oggetti nel filegroup.

    Nota di attenzioneAttenzione

    Utilizzare questa opzione solo quando il file è danneggiato e non è possibile ripristinarlo. Un file impostato su OFFLINE può essere riportato in linea solo tramite il ripristino del file dal backup. Per ulteriori informazioni sul ripristino di un singolo file, vedere RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Aggiunge, modifica o rimuove un filegroup nel database.

  • ADD FILEGROUP filegroup_name
    Aggiunge un filegroup nel database.

  • CONTAINS FILESTREAM
    Specifica che tramite il filegroup vengono archiviati oggetti binari di grandi dimensioni (BLOB) nel file system.

  • REMOVE FILEGROUP filegroup_name
    Rimuove un filegroup dal database. Il filegroup può essere rimosso solo se è vuoto. Rimuove tutti i file a partire dal filegroup. Per ulteriori informazioni, vedere "REMOVE FILE logical_file_name" più indietro in questo argomento.

  • MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
    Modifica il filegroup impostando lo stato su READ_ONLY o READ_WRITE, impostando il filegroup come predefinito per il database o modificando il nome del filegroup.

    • <filegroup_updatability_option>
      Imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.

    • DEFAULT
      Imposta il filegroup specificato in filegroup_name come nuovo filegroup predefinito del database. In un database può esistere un solo filegroup predefinito. Per ulteriori informazioni, vedere Informazioni su file e filegroup.

    • NAME = new_filegroup_name
      Modifica il nome del filegroup impostando il nome specificato in new_filegroup_name.

<filegroup_updatability_option>::=

Imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.

  • READ_ONLY | READONLY
    Specifica che il filegroup è di sola lettura. Non sono consentiti aggiornamenti degli oggetti nel filegroup. Non è possibile rendere di sola lettura il filegroup primario. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per ulteriori informazioni, vedere la clausola SINGLE_USER.

    I database di sola lettura non consentono modifiche dei dati e pertanto:

    • Non viene eseguito il recupero automatico all'avvio del sistema.

    • La compattazione del database non è possibile.

    • Non vengono attivati blocchi nei database di sola lettura e ciò può portare a migliori prestazioni di esecuzione delle query.

    [!NOTA]

    La parola chiave READONLY verrà rimossa a partire da una delle prossime versioni di MicrosoftSQL Server. Evitarne l'utilizzo in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare READ_ONLY in alternativa.

  • READ_WRITE | READWRITE
    Specifica che il filegroup è di lettura/scrittura. Sono consentiti aggiornamenti degli oggetti contenuti nel filegroup. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per ulteriori informazioni, vedere la clausola SINGLE_USER.

    [!NOTA]

    La parola chiave READWRITE verrà rimossa a partire da una delle prossime versioni di MicrosoftSQL Server. Evitarne l'utilizzo in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare READ_WRITE in alternativa.

Per determinare lo stato di queste opzioni, è possibile esaminare la colonna is_read_only nella vista del catalogo sys.databases oppure la proprietà Updateability della funzione DATABASEPROPERTYEX.

Osservazioni

Per ridurre le dimensioni di un database, utilizzare DBCC SHRINKDATABASE.

Non è possibile aggiungere o rimuovere file durante l'esecuzione di un'istruzione BACKUP.

Per ogni database è possibile specificare un massimo di 32.767 file e 32.767 filegroup.

In SQL Server 2005 o nelle versione successive, lo stato di un file di database, ad esempio in linea o non in linea, viene mantenuto indipendentemente dallo stato del database. Per ulteriori informazioni, vedere Stati dei file. Lo stato dei file all'interno di un filegroup determina la disponibilità dell'intero filegroup. Perché un filegroup sia disponibile, è necessario che tutti i file in esso inclusi siano in linea. Se un filegroup non è in linea, qualsiasi tentativo di accesso al filegroup tramite un'istruzione SQL avrà esito negativo e verrà generato un errore. Per la creazione di piani delle query per istruzioni SELECT, Query Optimizer evita gli indici non cluster e le viste indicizzate presenti in filegroup non in linea. Ciò consente la corretta esecuzione di tali istruzioni. Tuttavia, se il filegroup non in linea contiene l'heap o l'indice cluster della tabella di destinazione, l'istruzione SELECT avrà esito negativo. Avranno inoltre esito negativo anche tutte le eventuali istruzioni INSERT, UPDATE o DELETE che modificano una tabella con qualsiasi indice in un filegroup non in linea.

Spostamento di file

In SQL Server 2005 o versioni successive è possibile spostare file di dati e di log, di sistema o definiti dall'utente, specificando la nuova posizione in FILENAME. Questa procedura può risultare utile nei casi seguenti:

  • Recupero in caso di errore, ad esempio quando il database è in modalità sospetta o viene chiuso a causa di un errore hardware.

  • Rilocazione pianificata.

  • Rilocazione per attività pianificate di manutenzione dei dischi.

Per ulteriori informazioni, vedere Spostamento dei file del database.

Inizializzazione dei file

Per impostazione predefinita, i file di dati e di log vengono inizializzati tramite il riempimento con zeri quando si esegue una delle operazioni seguenti:

  • Creazione di un database.

  • Aggiunta di file a un database esistente.

  • Aumento delle dimensioni di un file esistente.

  • Ripristino di un database o un filegroup.

I file di dati possono essere inizializzati immediatamente. Ciò consente l'esecuzione rapida di queste operazioni sui file. Per ulteriori informazioni, vedere Inizializzazione di file di database.

Esempi

A. Aggiunta di un file a un database

Nell'esempio seguente viene aggiunto un file di dati da 5 MB al database AdventureWorks.

USE master;
GO
ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB

);
GO

B. Aggiunta di un filegroup con due file a un database

Nell'esempio seguente viene creato il filegroup Test1FG1 nel database AdventureWorks e vengono aggiunti due file da 5 MB al filegroup.

USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO

ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

C. Aggiunta di due file di log a un database

Nell'esempio seguente vengono aggiunti due file di log da 5 MB al database AdventureWorks.

USE master;
GO
ALTER DATABASE AdventureWorks 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D. Rimozione di un file da un database

Nell'esempio seguente viene rimosso uno dei file aggiunti nell'esempio B.

USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

E. Modifica di un file

Nell'esempio seguente vengono aumentate le dimensioni di uno dei file aggiunti nell'esempio B.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

F. Spostamento di un file in un diverso percorso

Nell'esempio seguente il file Test1dat2 creato nell'esempio A viene spostato in una nuova directory.

[!NOTA]

È necessario spostare fisicamente il file nella nuova directory prima di eseguire l'esempio. In seguito, interrompere e avviare l'istanza di SQL Server oppure impostare il database AdventureWorks su OFFLINE e quindi su ONLINE per implementare la modifica.

USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. Spostamento del database tempdb in un diverso percorso

Nell'esempio seguente viene spostato il database tempdb dal percorso corrente nel disco a un'altro percorso nel disco. Poiché tempdb viene ricreato a ogni avvio del servizio MSSQLSERVER, non è necessario spostare fisicamente i dati e i file di log. I file vengono creati quando il servizio viene riavviato nel passaggio 3. Fino a quando il servizio non viene riavviato, tempdb continua a funzionare nel percorso esistente.

  1. Determinare i nomi di file logici del database tempdb e il rispettivo percorso corrente su disco.

    SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

  2. Modificare il percorso di ogni file tramite ALTER DATABASE.

    USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO

  3. Interrompere e riavviare l'istanza di SQL Server.

  4. Verificare la modifica dei file.

    SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

  5. Eliminare i file tempdb.mdf e templog.ldf dai percorsi originali.

H. Impostazione di un filegroup come predefinito

Nell'esempio seguente il filegroup Test1FG1 creato nell'esempio B viene impostato come filegroup predefinito. Il filegroup PRIMARY viene quindi reimpostato come filegroup predefinito. Si noti che il nome PRIMARY deve essere delimitato da parentesi quadre o virgolette.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. Aggiunta di un filegroup utilizzando ALTER DATABASE

Nell'esempio seguente viene aggiunto un FILEGROUP che contiene la clausola FILESTREAM al database FileStreamPhotoDB.

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
ALTER database FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO

--Add a file for storing database photos to FILEGROUP 
ALTER database FileStreamPhotoDB
ADD FILE
(
    NAME= 'PhotoShoot1',
    FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
GO