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

Articolo
08/27/2013

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

Icona di collegamento a un argomento Convenzioni 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 Spostare file del database.

Per un filegroup FILESTREAM, NAME può essere modificato online. Sebbene FILENAME possa essere modificato online, 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 è offline, il filegroup padre sarà contrassegnato internamente come offline. Di conseguenza, ogni accesso a dati FILESTREAM all'interno del filegroup specifico avrà esito negativo.

[!NOTA]

Le opzioni <add_or_modify_files> non sono disponibili in un database indipendente.

<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.
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 è possibile impostare i parametri SIZE, MAXSIZE e FILEGROWTH se è specificato un percorso UNC per il file.
  
  [!NOTA]
  
  I database di sistema non possono trovarsi in directory di condivisione UNC.
  
  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 archiviati in file system compressi.
  
  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.
  
  Le proprietà SIZE 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. Quando viene specificato un file di log o un file di dati secondario ma non si specifica size per il file, Motore di database crea un file di 1 MB.
  
  È 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.
- 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. Non vi sono dimensioni massime se questa opzione viene specificata per un contenitore FILESTREAM, il quale continua a crescere finché il disco non è pieno.
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 offline e rende inaccessibili tutti gli oggetti nel filegroup.

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

[!NOTA]

Le opzioni <filespec> non sono disponibili in un database indipendente.

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

[!NOTA]

A meno che il Garbage Collector per FILESTREAM non abbia rimosso tutti i file da un contenitore FILESTREAM, l'operazione ALTER DATABASE REMOVE FILE per rimuovere un contenitore FILESTREAM avrà esito negativo e verrà restituito un errore. Vedere la sezione relativa alla rimozione del contenitore FILESTREAM in "Osservazioni" di seguito 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 Filegroup e file di database.
- 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 Microsoft SQL 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 Microsoft SQL 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 online o offline, 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 online. Se un filegroup è offline, qualsiasi tentativo di accesso al filegroup tramite un'istruzione SQL avrà esito negativo e verrà generato un errore. Per la compilazione di piani delle query per istruzioni SELECT, Query Optimizer evita gli indici non cluster e le viste indicizzate presenti in filegroup offline. Ciò consente la corretta esecuzione di tali istruzioni. Tuttavia, se il filegroup offline 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 offline.

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 Spostare 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 della dimensione 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.

Rimozione di un contenitore FILESTREAM

Anche se il contenitore FILESTREAM potrebbe essere stato svuotato mediante l'operazione "DBCC SHRINKFILE", potrebbe essere ancora necessario mantenere nel database i riferimenti ai file eliminati per vari motivi di manutenzione del sistema. sp_filestream_force_garbage_collection (Transact-SQL) eseguirà il Garbage Collector per FILESTREAM per rimuovere questi file al momento opportuno affinché sia sicuro. A meno che il Garbage Collector per FILESTREAM non abbia rimosso tutti i file da un contenitore FILESTREAM, l'operazione ALTER DATABASEREMOVE FILE avrà esito negativo per la rimozione di un contenitore FILESTREAM e verrà restituito un errore. È consigliabile utilizzare il processo seguente per rimuovere un contenitore FILESTREAM.

Eseguire DBCC SHRINKFILE (Transact-SQL) con l'opzione EMPTYFILE per spostare il contenuto attivo del contenitore in altri contenitori.
Assicurarsi che siano stati eseguiti i backup del log, nel modello di recupero FULL o BULK_LOGGED.
Assicurarsi che sia stato eseguito il processo di lettura log repliche, se rilevante.
Eseguire sp_filestream_force_garbage_collection (Transact-SQL) per forzare l'eliminazione tramite Garbage Collector di qualsiasi file non più necessario in questo contenitore.
Eseguire ALTER DATABASE con l'opzione REMOVE FILE per rimuovere questo contenitore.
Ripetere i passaggi da 2 a 4 ancora una volta per completare la Garbage Collection.
Utilizzare ALTER Database...REMOVE FILE per rimuovere questo contenitore.

Esempi

A.Aggiunta di un file a un database

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

USE master;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.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 AdventureWorks2012 e vengono aggiunti due file da 5 MB al filegroup.

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.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 AdventureWorks2012.

USE master;
GO
ALTER DATABASE AdventureWorks2012 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.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 AdventureWorks2012
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 AdventureWorks2012 
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, arrestare e avviare l'istanza di SQL Server oppure impostare il database AdventureWorks2012 su OFFLINE e quindi su ONLINE per implementare la modifica.

USE master;
GO
ALTER DATABASE AdventureWorks2012
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.

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
```

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

Arrestare e riavviare l'istanza di SQL Server.

Verificare la modifica dei file.

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

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 AdventureWorks2012 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2012 
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