Creare un backup completo del database

Articolo
03/12/2024

Si applica a: SQL Server

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

Per altre informazioni, vedere Backup e ripristino di SQL Server con Archiviazione BLOB di Azure e Backup di SQL Server nell’URL.

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 una panoramica approfondita dei concetti e delle attività di backup, vedere Panoramica del backup (SQL Server) prima di procedere.

Consigli

Con l'aumento delle dimensioni del database, i backup completi del database richiedono più tempo e più spazio di archiviazione. Per database di grandi dimensioni, valutare la possibilità di integrare un backup completo del database con una serie di backup di database differenziali.
Stimare la dimensione di un backup del database completo tramite la stored procedure di sistema 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 il backup viene eseguito di frequente, questi messaggi aumenteranno rapidamente, provocando la creazione di log degli errori di dimensioni elevate e rendendo difficile l'individuazione di altri messaggi. In questo caso è possibile eliminare le voci di log di backup usando il flag di traccia 3226 se nessuno degli script dipende da esse. 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 (11.x), le opzioni PASSWORD 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. Il servizio SQL Server deve leggere e scrivere sul dispositivo. L'account con il quale viene eseguito il servizio SQL Server deve avere autorizzazioni di scrittura sul dispositivo di backup. 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. I problemi relativi al file fisico del dispositivo di backup potrebbero emergere solo dopo l’utilizzo del backup o il tentativo di ripristino.

Utilizzo di SQL Server Management Studio

Nota

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

Dopo aver stabilito la connessione all'istanza appropriata del motore di database di Microsoft SQL Server, in Esplora oggetti espandere l'albero del server.
Espandere Databasee 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 di cui eseguire il backup, scegliere Attività e quindi selezionare Backup.
Nella finestra di dialogo Backup database il database selezionato viene visualizzato nell'elenco a discesa, che può essere sostituito con qualsiasi altro database nel server.
Nell'elenco a discesa Tipo di backup selezionare il tipo di backup. Il valore predefinito è Completo.

Importante

Prima di poter eseguire un backup differenziale o del log delle transazioni, è necessario eseguire almeno un backup completo del database.
In Componente di cui eseguire il backup selezionare Database.
Nella sezione Destinazione esaminare il percorso predefinito per il file di backup (nella cartella ../mssql/data).

È possibile usare l'elenco a discesa Backup su per selezionare un dispositivo diverso. Selezionare Aggiungi per aggiungere oggetti e/o destinazioni di backup. È possibile eseguire lo striping del set di backup su più file per aumentare la velocità di backup.

Per rimuovere una destinazione di backup, selezionarla, poi selezionare Rimuovi. Per visualizzare il contenuto di una destinazione di backup, selezionarla, poi selezionare Contenuto.
Rivedere le altre impostazioni disponibili nelle pagine Opzioni supporti e Opzioni di backup (facoltativo).

Per altre informazioni sulle varie opzioni di backup, vedere le pagine Generale, Opzioni supporti e Opzioni di backup.
Selezionare OK per avviare il backup.
Quando il backup viene completato correttamente, selezionare OK per chiudere la finestra di dialogo SQL Server Management Studio.

Informazioni aggiuntive

Dopo aver creato un backup completo del database, è possibile creare un backup differenziale del database o un backup del log delle transazioni.
È possibile selezionare la casella di controllo Backup di sola copia per creare un backup di sola copia (facoltativo). 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). Per il tipo di backup Differenziale non è possibile creare un backup di sola copia.
L'opzione Sovrascrivi supporti è disabilitata nella pagina Opzioni supporti se si esegue il backup su un URL.

Esempi

Per gli esempi seguenti, creare un database di test con il codice Transact-SQL seguente:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

R. Eseguire il backup completo su disco nel percorso predefinito

In questo esempio verrà eseguito il backup su disco del database SQLTestDB nel percorso di backup predefinito.

Dopo aver stabilito la connessione all'istanza appropriata del motore di database di Microsoft SQL Server, in Esplora oggetti espandere l'albero del server.
Espandere i database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività, quindi selezionare Backup....
Seleziona OK.
Quando il backup viene completato correttamente, selezionare OK per chiudere la finestra di dialogo SQL Server Management Studio.

Eseguire il backup SQL

B. Eseguire il backup completo su disco in un percorso non predefinito

In questo esempio verrà eseguito il backup su disco del database SQLTestDB nel percorso prescelto.

Dopo aver stabilito la connessione all'istanza appropriata del motore di database di Microsoft SQL Server, in Esplora oggetti espandere l'albero del server.
Espandere i database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività, quindi selezionare Backup....
Nella sezione Destinazione della pagina Generale selezionare Disco dall'elenco a discesa Backup su: .
Selezionare Rimuovi finché non vengono rimossi tutti i file di backup esistenti.
Selezionare Aggiungi. Verrà aperta la finestra di dialogo Selezionare la destinazione di backup.
Immettere un percorso e un nome file validi nella casella di testo Nome file e usare .bak come estensione per semplificare la classificazione del file.
Selezionare OK, poi selezionare di nuovo OK per avviare il backup.
Quando il backup viene completato correttamente, selezionare OK per chiudere la finestra di dialogo SQL Server Management Studio.

Modificare il percorso del database

C. Creare un backup crittografato

In questo esempio verrà eseguito il backup con crittografia del database SQLTestDB nel percorso di backup predefinito.

Dopo aver stabilito la connessione all'istanza appropriata del motore di database di Microsoft SQL Server, in Esplora oggetti espandere l'albero del server.
Espandere Database, espandere Database di sistema, fare clic con il pulsante destro del mouse su master e scegliere Nuova query per aprire una finestra di query con una connessione al database SQLTestDB.

Eseguire i comandi seguenti per creare una chiave master del database e un certificato nel database master.

-- Create the master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  

-- If the master key already exists, open it in the same session that you create the certificate (see next step)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'

-- Create the certificate encrypted by the master key
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';

In Esplora oggetti, nel nodo Database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività e quindi selezionare Backup.
Nella sezione Sovrascrivi supporti della pagina Opzioni supporti selezionare Esegui backup di un nuovo set di supporti e cancella tutti i set di backup esistenti.
Nella sezione Crittografia della pagina Opzioni di backup selezionare la casella di controllo Crittografa backup .
Nell'elenco a discesa Algoritmo selezionare AES 256.
Nell'elenco a discesa Certificato o chiave asimmetrica selezionare MyCertificate.
Seleziona OK.

Backup crittografato

D. Backup su Archiviazione BLOB di Azure

L’esempio crea un backup completo del database di SQLTestDB in Archiviazione BLOB di Azure. L’esempio presuppone che sia già disponibile un account di archiviazione con un contenitore BLOB. L’esempio crea automaticamente una firma di accesso condiviso. L'esecuzione dell'esempio non riesce se il contenitore ha una firma di accesso condiviso esistente.

Se non è disponibile un contenitore di Archiviazione BLOB di Azure in un account di archiviazione, crearne uno prima di continuare. Vedere Creare un account di archiviazione per utilizzo generico e Creare un contenitore.

Dopo aver stabilito la connessione all'istanza appropriata del motore di database di Microsoft SQL Server, in Esplora oggetti espandere l'albero del server.
Espandere i database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività, quindi selezionare Backup....
Nella pagina Generale nella sezione Destinazione selezionare l' URL dall'elenco a discesa Backup in: .
Selezionare Aggiungi. Verrà aperta la finestra di dialogo Selezionare la destinazione di backup.
Se in precedenza è stato registrato il contenitore di archiviazione di Azure che si vuole usare con SQL Server Management Studio, selezionarlo. In caso contrario, selezionare Nuovo contenitore per registrare un nuovo contenitore.
Nella finestra di dialogo Connetti a una sottoscrizione Microsoft accedere al proprio account.
Nella casella di testo dell'elenco a discesa Seleziona account di archiviazione selezionare l'account di archiviazione.
Nella casella di testo dell'elenco a discesa Seleziona contenitore BLOB selezionare il contenitore BLOB.
Nella casella del calendario dell'elenco a discesa Scadenza criteri di accesso condiviso selezionare una data di scadenza per i criteri di accesso condiviso creati in questo esempio.
Selezionare Crea credenziali per generare una firma di accesso condiviso e le credenziali in SQL Server Management Studio.
Selezionare OK per chiudere la finestra di dialogo Connetti a una sottoscrizione Microsoft.
Nella casella di testo File di backup modificare il nome del file di backup (facoltativo).
Selezionare OK per chiudere la finestra di dialogo Seleziona la destinazione di backup.
Selezionare OK per avviare il backup.
Quando il backup viene completato correttamente, selezionare OK per chiudere la finestra di dialogo SQL Server Management Studio.

Nota

Il backup di Archiviazione BLOB di Azure non è attualmente supportato tramite le identità gestite.

Utilizzo di Transact-SQL

Creare un backup completo del database eseguendo 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 opzioni_with [ ,...o ] ] ;

Opzione	Descrizione
database	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 ]	Consente di specificare una o più opzioni, o. Per informazioni su alcune opzioni WITH di base, vedere il passaggio 2.

Facoltativamente, specificare una o più opzioni WITH. Alcune opzioni WITH di base sono descritte di seguito. Per informazioni su tutte le opzioni WITH, vedere BACKUP (Transact-SQL).

Opzioni WITH del set di backup di base:

{ COMPRESSION | NO_COMPRESSION }: solo in SQL Server 2008 (10.0.x) Enterprise e versioni successive, specifica se la compressione dei backup viene applicata a questo backup, ignorando l'impostazione predefinita a livello di server.
ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY): Solo in SQL Server 2014 o versioni successive specificare l'algoritmo di crittografia da utilizzare e il certificato o la chiave asimmetrica da utilizzare 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.

Per impostazione predefinita, BACKUP accoda il backup a un set di supporti esistente, conservando i set di backup esistenti. Per specificarlo in modo esplicito, 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).

Usare l'opzione FORMAT per formattare i supporti di backup:

FORMAT [ , MEDIANAME = { nome_supporto | @variabile_nome_supporto } ] [ , MEDIADESCRIPTION = { testo | @variabile_testo } ]

Usare la clausola FORMAT, se i supporti vengono usati per la prima volta o si vogliono sovrascrivere tutti i dati esistenti. Facoltativamente, assegnare al nuovo supporto un nome e una descrizione.

Importante

Usare la clausola FORMAT dell'istruzione BACKUP con estrema cautela, in quanto entrambe comportano la cancellazione di eventuali backup archiviati in precedenza nei supporti di backup.

Esempi

Per gli esempi seguenti, creare un database di test con il codice Transact-SQL seguente:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

R. Backup su un dispositivo disco

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

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. Backup su un dispositivo nastro

Nell'esempio seguente viene eseguito il backup completo su nastro del database SQLTestDB , accodandolo ai backup precedenti.

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

C. Backup su un dispositivo nastro logico

Nell'esempio seguente viene creato in un dispositivo di backup logico per un'unità nastro. Nell'esempio viene quindi eseguito il backup completo del database SQLTestDB su quel dispositivo.

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

Con PowerShell

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

Nota

Questi esempi richiedono il modulo SqlServer. Per determinare se è installato, eseguire Get-Module -Name SqlServer. Per procedere all’installazione, eseguire Install-Module -Name SqlServer in una sessione di amministrazione di PowerShell.

Per altre informazioni, vedere Provider PowerShell per SQL Server.

Importante

Se si apre una finestra di PowerShell da SQL Server Management Studio per connettersi a un'installazione di SQL Server, è possibile omettere la parte relativa alle credenziali perché vengono usate automaticamente le credenziali in SSMS per stabilire la connessione tra PowerShell e l'istanza di SQL Server.

Esempi

R. Backup completo (locale)

L'esempio seguente consente di creare un backup di database completo del database di <myDatabase> nel percorso di backup predefinito dell'istanza del server Computer\Instance. Facoltativamente, questo esempio specifica -BackupAction Database.

Per esempi di sintassi completa, vedere Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Backup completo in Azure

Nell'esempio seguente viene creato un backup completo del database <myDatabase> sull'istanza <myServer> per Archiviazione BLOB di Azure. Sono stati creati i criteri di accesso archiviati con diritti di lettura, scrittura ed elenco. Le credenziali di SQL Server, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, sono stati creati usando una firma di accesso condiviso associata a criteri di accesso archiviati. Il comando di PowerShell usa il parametro BackupFile per specificare il percorso (URL) e il nome del file di backup.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Condividi tramite

Creare un backup completo del database

Limitazioni e restrizioni

Consigli

Sicurezza

Autorizzazioni

Utilizzo di SQL Server Management Studio

Informazioni aggiuntive

Esempi

R. Eseguire il backup completo su disco nel percorso predefinito

B. Eseguire il backup completo su disco in un percorso non predefinito

C. Creare un backup crittografato

D. Backup su Archiviazione BLOB di Azure

Utilizzo di Transact-SQL

Esempi

R. Backup su un dispositivo disco

B. Backup su un dispositivo nastro

C. Backup su un dispositivo nastro logico

Con PowerShell

Esempi

R. Backup completo (locale)

B. Backup completo in Azure

Attività correlate

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

Creare un backup completo del database

Limitazioni e restrizioni

Consigli

Sicurezza

Autorizzazioni

Utilizzo di SQL Server Management Studio

Informazioni aggiuntive

Esempi

R. Eseguire il backup completo su disco nel percorso predefinito

B. Eseguire il backup completo su disco in un percorso non predefinito

C. Creare un backup crittografato

D. Backup su Archiviazione BLOB di Azure

Utilizzo di Transact-SQL

Esempi

R. Backup su un dispositivo disco

B. Backup su un dispositivo nastro

C. Backup su un dispositivo nastro logico

Con PowerShell

Esempi

R. Backup completo (locale)

B. Backup completo in Azure

Attività correlate

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive