Creare un backup completo del database (SQL Server)

Articolo
2017-06-13

Questo argomento descrive come creare un backup completo del database in SQL Server 2014 usando SQL Server Management Studio, Transact-SQL o PowerShell.

Annotazioni

Per informazioni sul backup di SQL Server nel servizio di archiviazione BLOB di Azure, vedere Backup e ripristino di SQL Server con il servizio di archiviazione BLOB di Azure.

Contenuto dell'articolo

Prima di iniziare:

Limitazioni e restrizioni

Raccomandazioni

sicurezza
Per creare un backup completo del database utilizzando:

SQL Server Management Studio

Transact-SQL

PowerShell
Attività correlate

Prima di iniziare

Limitazioni e restrizioni

Non è possibile usare l'istruzione BACKUP in una transazione esplicita o implicita .
I backup creati nella versione più recente di SQL Server non possono essere ripristinati nelle versioni precedenti di SQL Server.
Per altre informazioni, vedere Panoramica del backup (SQL Server).

Consigli

Man mano che aumentano le dimensioni dei backup completi del database, il completamento richiede più tempo e richiede più spazio di archiviazione. Per un database di grandi dimensioni può pertanto essere utile integrare un backup completo del database con una serie di backup database differenziali. Per altre informazioni, vedere Backup differenziali (SQL Server).
È possibile stimare le dimensioni di un backup completo del database usando la procedura di sistema memorizzata sp_spaceused.
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 si esegue il backup del log molto frequentemente, questi messaggi di operazione riuscita si accumulano rapidamente, generando log di errore enormi che possono rendere difficile trovare altri messaggi. In questi casi è possibile eliminare queste voci di log usando il flag di traccia 3226 se nessuno degli script dipende da tali voci. Per altre informazioni, vedere Flag di traccia (Transact-SQL).

Sicurezza

TRUSTWORTHY è impostato su OFF in un backup del database. Per informazioni su come impostare TRUSTWORTHY su ON, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

A partire da SQL Server 2012 le PASSWORD opzioni e MEDIAPASSWORD non sono più disponibili per la creazione di backup. È possibile ripristinare backup creati con password.

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. SQL Server deve essere in grado di leggere e scrivere nel dispositivo; l'account con il quale viene eseguito il servizio SQL Server deve avere autorizzazioni di scrittura. Le autorizzazioni di accesso ai file, tuttavia, non vengono controllate dalla stored procedure sp_addumpdeviceche aggiunge una voce per un dispositivo di backup nelle tabelle di sistema. Tali problemi sul file fisico del dispositivo di backup potrebbero non apparire fino a quando non si accede alla risorsa fisica durante un tentativo di backup o ripristino.

Uso di SQL Server Management Studio

Annotazioni

Quando si specifica un'attività di backup usando SQL Server Management Studio, è possibile generare lo script Transact-SQL BACKUP corrispondente facendo clic sul pulsante Script e selezionando una destinazione script.

Per eseguire il backup di un database

Dopo aver stabilito la connessione all'istanza appropriata del motore di database di Microsoft SQL Server, in Esplora oggetti fai clic sul nome del server per espanderne l'albero.
Espandere Databasee, 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àe quindi fare clic su Backup. Verrà visualizzata la finestra di dialogo Backup database .
Nella casella di Database elenco, verificare il nome del database. È possibile selezionare facoltativamente un database diverso nell'elenco.
È possibile eseguire un backup del database per qualsiasi modello di recupero (FULL, BULK_LOGGED o SIMPLE).
Nella casella di riepilogo Tipo di backup selezionare Completo.

Si noti che dopo aver creato un backup completo del database, è possibile creare un backup differenziale del database; Per altre informazioni, vedere Creare un backup differenziale del database (SQL Server).
Facoltativamente, è possibile selezionare Backup di sola copia per creare un backup di sola copia. Un backup di sola copia è un backup di SQL Server indipendente dalla sequenza dei backup convenzionali di SQL Server. Per altre informazioni, vedere Backup di sola copia (SQL Server).

Annotazioni

Quando si seleziona l'opzione Differenziale , non è possibile creare un backup di sola copia.
Per Componente di backup fare clic su Database.
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 del set di backup nella casella di testo Descrizione .
Scegliere il tipo di destinazione di backup facendo clic su Disco, Nastro o URL. 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 nella casella di riepilogo Backup in.

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 multimediali, fare clic su Opzioni multimediali nel riquadro Seleziona una pagina .
Selezionare un'opzione Sovrascrivi supporti facendo clic su una delle seguenti:
- Esegui backup sul 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 altre informazioni, vedere Set di supporti, gruppi di supporti e set di backup (SQL Server).
  
  Facoltativamente, seleziona l'opzione Controlla nome set di supporti e scadenza set di backup per fare in modo che l'operazione di backup verifichi la data e l'ora in cui scadono il set di supporti e il set di backup.
  
  Facoltativamente, inserisci 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 un nome per il set di supporti, il supporto (nastro o disco) verrà controllato per verificare che il nome effettivo corrisponda al nome specificato.
  
  Importante
  
  Questa opzione è disabilitata se è stato selezionato l'URL come destinazione di backup nella pagina Generale . Per altre informazioni, vedere Eseguire il backup del database (pagina Opzioni supporti)
  
  Se si prevede di usare la crittografia, non selezionare questa opzione. Se si seleziona questa opzione, le opzioni di crittografia nella pagina Opzioni di backup verranno disabilitate. La crittografia non è supportata quando si aggiunge al set di backup esistente.
- Esegui backup su 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 .
  
  Importante
  
  Questa opzione è disabilitata se è stato selezionato l'URL nella pagina Generale . Queste azioni non sono supportate durante il backup nell'archiviazione di Azure.
Nella sezione Affidabilità controllare facoltativamente:
- Verifica backup al termine.
- Esegui checksum prima della scrittura su supporti e, facoltativamente, Continua in caso di errore checksum. Per informazioni sui checksum, consultare 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. Se si seleziona questa opzione, verrà inoltre attivata l'opzione Riavvolgi il nastro prima di scaricarlo .

Annotazioni

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 backup nella pagina Generale .
Per visualizzare o selezionare le opzioni di backup, fare clic su Opzioni di backup nel riquadro Seleziona una pagina .
Specificare quando il set di backup scadrà e può essere sovrascritto senza ignorare in modo esplicito la verifica dei dati di scadenza:
- Per impostare la scadenza del set di backup dopo un numero di giorni specifico, fare clic su Dopo (opzione predefinita) e immettere il numero di giorni dopo la creazione del set trascorsi i quali il set scadrà. È 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 è impostato nell'opzione Conservazione supporti di backup predefinita (in giorni) della finestra di dialogo Proprietà server (pagina Impostazioni database). Per accedere alla pagina, fare clic con il pulsante destro del mouse sul nome del server in Esplora oggetti e scegliere Proprietà, quindi selezionare la pagina Impostazioni database .
- Per impostare una data di scadenza specifica per il set di backup, fare clic su Ile immettere la data di scadenza del set.
  
  Per altre informazioni sulle date di scadenza del backup, vedere BACKUP (Transact-SQL).
SQL Server 2008 Enterprise e versioni successive supporta la compressione dei backup. Per impostazione predefinita, la compressione di un backup dipende dal valore dell'opzione di configurazione del server Valore predefinito di compressione backup. Tuttavia, indipendentemente dall'impostazione predefinita a livello di server corrente, è possibile comprimere un backup selezionando Comprimi backuped è possibile impedire la compressione selezionando Non comprimere il backup.

Per visualizzare o modificare il valore predefinito di compressione dei backup corrente
- Visualizzare o configurare l'opzione di configurazione del server di default per la compressione del backup
Specificare se usare la crittografia per il backup. Selezionare un algoritmo di crittografia da utilizzare per il passaggio di crittografia e specificare un certificato o una chiave asimmetrica da un elenco di chiavi asimmetriche o di certificati esistenti. La crittografia è supportata in SQL Server 2014 o versione successiva. Per altre informazioni sulle opzioni di crittografia, vedere Backup del database (pagina Opzioni di backup).

Annotazioni

In alternativa, è possibile utilizzare la Creazione guidata per i piani di manutenzione per creare backup del database.

Uso di Transact-SQL

Per creare un backup completo del database

Eseguire l'istruzione BACKUP DATABASE per creare il backup completo del database, specificando:

Il nome del database di cui eseguire il backup.
Il dispositivo di backup in cui archiviare il backup completo del database.

La sintassi di base dell'istruzione Transact-SQL per un backup completo del database è la seguente:

BACKUP DATABASE database

TO dispositivo_backup [ ,...n ]

[ WITH con_opzioni [ ,...o ] ];

Opzione	Descrizione
banca dati	Corrisponde al database di cui eseguire il backup.
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 altre informazioni, vedere Dispositivi di backup (SQL Server).
WITH con_opzioni [ ,...o ]	Facoltativamente, specifica una o più opzioni aggiuntive, o. Per informazioni su alcune opzioni di base, vedere il passaggio 2.

Facoltativamente, specificare una o più opzioni WITH. Di seguito sono descritte alcune opzioni WITH di base. Per informazioni su tutte le opzioni WITH, vedere BACKUP (Transact-SQL).
- Set di backup di base con opzioni
  
  { COMPRESSIONE | NESSUNA_COMPRESSIONE }
  In SQL Server 2008 Enterprise e versioni successive specifica se la compressione dei backup viene eseguita su questo backup, eseguendo l'override del valore predefinito a livello di server.
  
  CRIPTAZIONE (ALGORITMO, CERTIFICATO SERVER | CHIAVE ASIMMETRICA)
  Solo in SQL Server 2014 o versione successiva specificare l'algoritmo di crittografia da usare e la chiave certificato o asimmetrica da usare per proteggere la crittografia.
  
  DESCRIPTION = { 'text' | @text_variable }
  Specifica il testo in formato libero che descrive il set di backup. La stringa può essere composta da un massimo di 255 caratteri.
  
  NAME = { backup_set_name | @backup_set_name_var }
  Specifica il nome del set di backup. I nomi possono essere composti da un massimo di 128 caratteri. Se si omette NAME, al set di backup non viene assegnato alcun nome specifico.
- Set di backup di base con opzioni:
  
  Per impostazione predefinita, BACKUP aggiunge il backup a un set di supporti esistente, mantenendo i set di backup esistenti. Per specificare in modo esplicito questa opzione, usare l'opzione NOINIT. Per informazioni sull'accodamento a set di backup esistenti, vedere Set di supporti, gruppi di supporti e set di backup (SQL Server).
  
  In alternativa, per formattare il supporto di backup, usare l'opzione FORMAT:
  
  FORMAT [ , MEDIANAME**=** { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
  Usare la clausola FORMAT quando si utilizza il supporto per la prima volta o si desidera sovrascrivere tutti i dati esistenti. Facoltativamente, assegnare al nuovo supporto un nome e una descrizione.
  
  Importante
  
  Prestare particolare attenzione quando si usa la clausola FORMAT dell'istruzione BACKUP perché elimina definitivamente tutti i backup archiviati in precedenza nel supporto di backup.

Esempi (Transact-SQL)

A. Fare il backup su un'unità disco

Nell'esempio seguente, viene eseguito il backup completo del database AdventureWorks2012 su disco, utilizzando FORMAT per creare un nuovo set di supporti.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_SQLServerBackups',  
      NAME = 'Full Backup of AdventureWorks2012';  
GO

B. Backup su un dispositivo nastro

Nell'esempio seguente viene eseguito il backup del database AdventureWorks2012completo su nastro, aggiungendo il backup ai backup precedenti.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO TAPE = '\\.\Tape0'  
   WITH NOINIT,  
      NAME = 'Full Backup of AdventureWorks2012';  
GO

C. Effettuare il backup su un dispositivo nastro logico

Nell'esempio seguente viene creato un dispositivo di backup logico per un'unità nastro. Nell'esempio viene quindi eseguito il backup del database AdventureWorks2012 completo nel dispositivo.

-- Create a logical backup device,   
-- AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.  
USE master;  
GO  
EXEC sp_addumpdevice 'tape', 'AdventureWorks2012_Bak_Tape', '\\.\tape0'; USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO AdventureWorks2012_Bak_Tape  
   WITH FORMAT,  
      MEDIANAME = 'AdventureWorks2012_Bak_Tape',  
      MEDIADESCRIPTION = '\\.\tape0',   
      NAME = 'Full Backup of AdventureWorks2012';  
GO

Utilizzo di PowerShell

Usare il cmdlet Backup-SqlDatabase. Per indicare in modo esplicito che si tratta di un backup completo del database, specificare il parametro -BackupAction con il relativo valore predefinito, Database. Questo parametro è facoltativo per i backup completi di database.

L'esempio seguente consente di creare un backup di database completo del database di MyDB nel percorso di backup predefinito dell'istanza del server Computer\Instance. In questo esempio viene specificato facoltativamente -BackupAction Database.
```
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  
```

Per impostare e utilizzare il provider PowerShell per SQL Server

Provider di PowerShell di SQL Server

Attività correlate

Vedere anche

Panoramica del backup (SQL Server)
Backup del log delle transazioni (SQL Server)
Set di supporti, famiglie di supporti e insiemi di backup (SQL Server)
sp_addumpdevice (Transact-SQL)
BACKUP (Transact-SQL)
Backup database (pagina Generale)
Backup database (pagina Opzioni di backup)
Backup differenziali (SQL Server)
Backup completo del database (SQL Server)

Condividi tramite

Creare un backup completo del database (SQL Server)

Prima di iniziare

Limitazioni e restrizioni

Consigli

Sicurezza

Autorizzazioni

Uso di SQL Server Management Studio

Per eseguire il backup di un database

Uso di Transact-SQL

Per creare un backup completo del database

Esempi (Transact-SQL)

A. Fare il backup su un'unità disco

B. Backup su un dispositivo nastro

C. Effettuare il backup su un dispositivo nastro logico

Utilizzo di PowerShell

Attività correlate

Vedere anche

Risorse aggiuntive