Backup di file e filegroup (SQL Server)

Articolo
06/25/2013

In questo argomento viene descritto come eseguire il backup di file e filegroup in SQL Server 2012 utilizzando SQL Server Management Studio, Transact-SQL o PowerShell. Quando a causa delle dimensioni del database e dei requisiti relativi alle prestazioni non è consigliabile eseguire un backup completo del database, è possibile creare invece un backup del file. Un backup del file contiene tutti i dati inclusi in uno o più file (o filegroup). Per ulteriori informazioni sui backup del file, vedere Backup completi del file (SQL Server) e Backup differenziali [SQL Server].

Contenuto dell'argomento

Prima di iniziare:

Limitazioni e restrizioni

Consigli

Sicurezza
Per eseguire il backup di file e filegroup utilizzando:

SQL Server Management Studio

Transact-SQL

PowerShell

Prima di iniziare

Limitazioni e restrizioni

Non è possibile utilizzare l'istruzione BACKUP in una transazione esplicita o implicita.
In base al modello di recupero con registrazione minima, è necessario creare una copia di backup contenente tutti i file di lettura/scrittura, al fine di garantire che il database possa essere ripristinato fino a un punto nel tempo coerente. Anziché specificare singolarmente ogni file o filegroup di lettura/scrittura, utilizzare l'opzione READ_WRITE_FILEGROUPS. Questa opzione consente di eseguire il backup di tutti i filegroup di lettura/scrittura del database. Un backup creato specificando READ_WRITE_FILEGROUPS è noto come backup parziale. Per ulteriori informazioni, vedere Backup parziali (SQL Server).
Per ulteriori informazioni sulle limitazioni e restrizioni, vedere Panoramica del backup (SQL Server).

Consigli

Per impostazione predefinita, per ogni operazione di backup eseguita in modo corretto viene aggiunta una voce al log degli errori di SQL Server e al registro eventi di sistema. Se il backup del log viene eseguito di frequente, questi messaggi possono aumentare rapidamente, provocando la creazione di log degli errori di dimensioni elevate e rendendo difficile l'individuazione di altri messaggi. In questi casi è possibile eliminare tali voci di log utilizzando il flag di traccia 3226 se nessuno degli script dipende da esse. Per ulteriori informazioni, vedere Flag di traccia (Transact-SQL).

Sicurezza

Autorizzazioni

Le autorizzazioni BACKUP DATABASE e BACKUP LOG vengono assegnate per impostazione predefinita ai membri del ruolo predefinito del server sysadmin e dei ruoli predefiniti del database db_owner e db_backupoperator.

Eventuali problemi correlati alla proprietà e alle autorizzazioni sul file fisico del dispositivo di backup possono interferire con l'operazione di backup. È necessario che in SQL Server sia possibile leggere e scrivere sul dispositivo e che l'account utilizzato per eseguire il servizio SQL Server disponga delle autorizzazioni di scrittura. Le autorizzazioni di accesso ai file, tuttavia, non vengono controllate dalla stored procedure sp_addumpdevice, che aggiunge una voce per un dispositivo di backup nelle tabelle di sistema. Di conseguenza, i problemi relativi all'accesso e alla proprietà del file fisico del dispositivo di backup potrebbero emergere solo in fase di accesso alla risorsa fisica durante un tentativo di backup o ripristino.

Icona freccia utilizzata con il collegamento Torna all'inizio [Inizio pagina]

Utilizzo di SQL Server Management Studio

Per eseguire il backup di file e filegroup del database

Dopo aver effettuato la connessione all'istanza appropriata del Motore di database di SQL Server, in Esplora oggetti fare clic sul nome del server per espanderne l'albero.
Espandere Database e, a seconda del database, selezionare un database utente o espandere Database di sistema e selezionare un database di sistema.
Fare clic con il pulsante destro del mouse sul database, scegliere Attività, quindi fare clic su Backup. Verrà visualizzata la finestra di dialogo Backup database.
Verificare il nome del database nell'elenco Database. È possibile selezionare facoltativamente un database diverso nell'elenco.
Nell'elenco Tipo di backup selezionare Completo o Differenziale.
Selezionare l'opzione File e filegroup in Componente di cui eseguire il backup.
Selezionare i file e i filegroup dei quali si desidera eseguire il backup nella finestra di dialogo Seleziona file e filegroup. È possibile selezionare uno o più file singoli oppure selezionare la casella di controllo relativa a un filegroup per selezionare automaticamente tutti i file appartenenti al filegroup.
Accettare il nome predefinito del set di backup indicato nella casella di testo Nome oppure immettere un nome diverso per il set di backup.
Facoltativamente, immettere una descrizione per il set di backup nella casella di testo Descrizione.
Specificare la scadenza del set di backup:
- Per far scadere il set di backup dopo un numero specifico di giorni, fare clic su In seguito (opzione predefinita), quindi immettere il numero di giorni che devono trascorrere prima della scadenza del set. È possibile impostare un valore compreso nell'intervallo da 0 a 99999 giorni. L'impostazione del valore 0 giorni indica che il set di backup non ha scadenza.
  
  Il valore predefinito viene impostato nell'opzione Periodo di memorizzazione predefinito supporti di backup (giorni) della finestra di dialogo Proprietà server (pagina Impostazioni database). Per accedere a questa opzione, fare clic con il pulsante destro del mouse sul nome del server in Esplora oggetti, scegliere Proprietà e quindi la pagina Impostazioni database.
- Per impostare una data di scadenza specifica per il set di backup, fare clic su Il e immettere la data di scadenza del set.
Fare clic su Disco o Nastro per selezionare il tipo di destinazione del backup. Per selezionare i percorsi per un massimo di 64 unità disco o nastro contenenti un singolo set di supporti, fare clic su Aggiungi. I percorsi selezionati vengono visualizzati nell'elenco Backup su.

[!NOTA]

Per rimuovere una destinazione di backup, selezionarla e fare clic su Rimuovi. Per visualizzare il contenuto di una destinazione di backup, selezionarla e fare clic su Contenuto.
Per visualizzare o selezionare le opzioni avanzate, fare clic su Opzioni nel riquadro Selezione pagina.
Selezionare un'opzione Sovrascrivi supporti facendo clic su una delle opzioni seguenti:
- Esegui backup nel set di supporti esistente
  
  Per questa opzione, fare clic su Accoda al set di backup esistente o Sovrascrivi tutti i set di backup esistenti. Per informazioni sul backup in un set di supporti esistente, vedere Set di supporti, gruppi di supporti e set di backup (SQL Server).
  
  Selezionare facoltativamente l'opzione Controlla nome set di supporti e scadenza set di backup per impostare la verifica della data e dell'ora di scadenza del set di supporti e del set di backup durante l'operazione di backup.
  
  Immettere facoltativamente un nome nella casella di testo Nome set di supporti. Se non si specifica un nome, verrà creato un set di supporti con nome vuoto. Se si specifica il nome di un set di supporti, viene eseguito un controllo sul supporto (nastro o disco) per verificare che il nome effettivo corrisponda al nome qui immesso.
  
  Se non si specifica il nome del set di supporti e si seleziona la casella di controllo per il controllo del nome, in caso di esito positivo anche il nome dei supporti nei supporti risulterà vuoto.
- Esegui backup in un nuovo set di supporti e cancella tutti i set di backup esistenti
  
  Per questa opzione, immettere un nome nella casella di testo Nome nuovo set di supporti e, facoltativamente, aggiungere una descrizione per il set di supporti nella casella di testo Descrizione nuovo set di supporti. Per ulteriori informazioni sulla creazione di un nuovo set di supporti, vedere Set di supporti, gruppi di supporti e set di backup (SQL Server).
Nella sezione Affidabilità è possibile selezionare facoltativamente:
- Verifica backup al termine.
- Esegui checksum prima della scrittura nei supporti e, facoltativamente, Continua in caso di errori checksum. Per ulteriori informazioni sui checksum, vedere Possibili errori relativi ai supporti durante il backup e il ripristino (SQL Server).
Se si esegue il backup su un'unità nastro (come specificato nella sezione Destinazione della pagina Generale) l'opzione Scarica nastro al termine del backup sarà attiva. Selezionando questa opzione viene abilitata anche l'opzione Riavvolgi il nastro prima di scaricarlo.

[!NOTA]

Le opzioni presenti nella sezione Log delle transazioni sono attive solo in caso di backup di un log delle transazioni (come specificato nella sezione Tipo di backup della pagina Generale).
In SQL Server 2008 Enterprise e nelle versioni più recenti è supportata la compressione dei backup. Per impostazione predefinita, la compressione di backup dipende dal valore dell'opzione di configurazione del server backup-compression default. Tuttavia, indipendentemente dall'impostazione predefinita a livello di server corrente, è possibile comprimere un backup selezionando Comprimi backup ed è possibile impedire la compressione selezionando Non comprimere il backup.

Per visualizzare l'impostazione predefinita corrente della compressione dei backup
- Visualizzare o configurare l'opzione di configurazione del server backup compression default

Icona freccia utilizzata con il collegamento Torna all'inizio [Inizio pagina]

Utilizzo di Transact-SQL

Per eseguire il backup di file e filegroup

Per creare un backup del file o del filegroup, utilizzare un'istruzione BACKUP DATABASE <file_or_filegroup>. In questa istruzione è necessario specificare almeno gli elementi seguenti:

Il nome del database.
Una clausola FILE o FILEGROUP per ogni file o filegroup, rispettivamente.
Il dispositivo di backup in cui verrà scritto il backup completo.

La sintassi Transact-SQL di base per un backup del file è la seguente:

BACKUP DATABASE database

{ FILE = logical_file_name | FILEGROUP = logical_filegroup_name } [ ,...f ]

TO backup_device [ ,...n ]

[ WITH with_options [ ,...o ] ] ;

Opzione	Descrizione
database	Nome del database di cui viene eseguito il backup del log delle transazioni oppure il backup parziale o completo del database.
FILE = logical_file_name	Specifica il nome logico di un file da includere nel backup del file.
FILEGROUP = logical_filegroup_name	Specifica il nome logico di un filegroup da includere nel backup del file. Con il modello di recupero con registrazione minima, il backup dei filegroup è consentito solo per i filegroup di sola lettura.
[ ,...f ]	Segnaposto che indica che è possibile specificare più file e filegroup. Il numero di file o filegroup che possono essere specificati è illimitato.
backup_device [ ,...n ]	Specifica un elenco di dispositivi di backup da 1 a 64 da utilizzare per l'operazione di backup. È possibile specificare un dispositivo di backup fisico oppure un dispositivo di backup logico corrispondente se è già stata definito. Per specificare un dispositivo di backup fisico, utilizzare l'opzione DISK o TAPE: { DISK \| TAPE } = physical_backup_device_name Per ulteriori informazioni, vedere Dispositivi di backup (SQL Server).
WITH with_options [ ,...o ]	Facoltativamente, specifica una o più opzioni aggiuntive, ad esempio DIFFERENTIAL. [!NOTA] Il backup differenziale del file richiede come base un backup completo del file. Per ulteriori informazioni, vedere Creazione di un backup differenziale del database (SQL Server).

Se si utilizza il modello di recupero con registrazione completa, è inoltre necessario eseguire un backup del log delle transazioni. Per utilizzare un set completo di backup del file completi per il ripristino di un database, è inoltre necessario disporre di backup dei log relativi a tutti i backup del file, dall'inizio del primo backup del file. Per ulteriori informazioni, vedere Backup di un log delle transazioni (SQL Server).

Esempi (Transact-SQL)

Negli esempi seguenti viene eseguito il backup di uno o più file dei filegroup secondari del database Sales. Questo database utilizza il modello di recupero con registrazione completa e contiene i filegroup secondari seguenti:

Un filegroup denominato SalesGroup1 che include i file SGrp1Fi1 e SGrp1Fi2.
Un filegroup denominato SalesGroup2 che include i file SGrp2Fi1 e SGrp2Fi2.

A.Creazione di un backup del file per due file

Nell'esempio seguente viene creato un backup differenziale del file solo per il file SGrp1Fi2 del filegroup SalesGroup1 e per il file SGrp2Fi2 del filegroup SalesGroup2.

--Backup the files in the SalesGroup1 secondary filegroup.
BACKUP DATABASE Sales
   FILE = 'SGrp1Fi2', 
   FILE = 'SGrp2Fi2' 
   TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck';
GO

B.Creazione di un backup completo dei filegroup secondari

Nell'esempio seguente viene creato un backup del file completo per ogni file di entrambi i filegroup secondari.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck';
GO

C.Creazione di un backup differenziale dei filegroup secondari

Nell'esempio seguente viene creato un backup differenziale di ogni file presente in entrambi i filegroup secondari.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
   WITH 
      DIFFERENTIAL;
GO

Icona freccia utilizzata con il collegamento Torna all'inizio [Inizio pagina]

Utilizzo di PowerShell

Utilizzare il cmdlet Backup-SqlDatabase e specificare Files per il valore del parametro -BackupAction. Inoltre, specificare uno dei parametri seguenti:
- Per eseguire il backup di un determinato file, specificare il parametro -DatabaseFile String, dove String rappresenta uno o più file di database di cui eseguire il backup.
- Per eseguire il backup di tutti i file di un determinato filegroup, specificare il parametro -DatabaseFileGroup String, dove String rappresenta uno o più filegroup di database di cui eseguire il backup.
Nell'esempio seguente viene creato un backup completo di ogni file presente nei filegroup secondari 'FileGroup1' e 'FileGroup2' nel database MyDB. I backup vengono creati nel percorso di backup predefinito dell'istanza del server Computer\Instance.
```
--Enter this command at the PowerShell command prompt, C:\PS>
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Files -DatabaseFileGroup "FileGroup1","FileGroup2"
```

Per impostare e utilizzare il provider PowerShell per SQL Server