Creare un backup completo del database

Si applica a:SQL Server

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

Per altre informazioni, vedi Backup e ripristino di SQL Server con l'Archiviazione BLOB di Azure e Backup di SQL Server in URL per l'Archiviazione BLOB di Azure. Per una panoramica dei concetti e dei compiti di backup, vedi Panoramica di Backup (SQL Server).

Consigli

• Man mano che un database cresce, i backup completi del database richiedono più tempo e più spazio di archiviazione. Per i database di grandi dimensioni, integrare i backup completi con una serie di backup differenziali del database.

• Stima la dimensione di un backup completo del database con la procedura sp_spaceused sistema di archiviazione.

• Di default, ogni backup riuscito aggiunge una voce al log errori di SQL Server e al log eventi di sistema. Backup frequenti possono riempire questi log e rendere difficile trovare altri messaggi.

  Per sopprimere le voci di registro dei backup, usa il flag di traccia 3226, a condizione che i tuoi script non dipendano da esse.

Limitazioni

Non puoi usare l'istruzione BACKUP in una transazione esplicita o implicita.

Non puoi ripristinare i backup dalle versioni più recenti di SQL Server su versioni precedenti. Ad esempio, non puoi ripristinare un backup di SQL Server 2025 (17.x) su SQL Server 2022 (16.x).

Sicurezza

TRUSTWORTHY è impostato su OFF in un backup del database. Per impostare TRUSTWORTHY su ON, vediALTER DATABASE SET opzioni.

A partire da SQL Server 2012 (11.x), le PASSWORD opzioni e MEDIAPASSWORD non sono disponibili per creare backup. È possibile ripristinare backup creati con password.

Autorizzazioni

BACKUP DATABASE e BACKUP LOG sono autorizzazioni predefinite per i membri del ruolo del server predefinito sysadmin e dei ruoli predefiniti del database db_owner e db_backupoperator.

L'account del servizio SQL Server deve avere permessi di lettura e scrittura sul file fisico del dispositivo di backup. Problemi di proprietà o permessi su quel file impediscono le operazioni di backup e ripristino, e compaiono solo quando viene eseguita l'operazione di backup o ripristino.

Ad esempio, la procedura di archiviazione sp_addumpdevice sistema, che aggiunge una voce di dispositivo di backup alle tabelle di sistema, non convalida l'accesso ai file.

Usa SQL Server Management Studio

1. In Esplora oggetti, collegati al motore di database e poi espandi l'albero server.

2. Espandi Database, poi seleziona un database utente. Oppure, espandi i database di sistema e seleziona un database di sistema.

3. Clicca con il tasto destro sul database che vuoi fare il backup, punta a Task e poi seleziona Backup....

4. Nella finestra di dialogo Backup Database , il database selezionato appare nella lista a tendina. Puoi cambiare il database con qualsiasi altro database sul server.

5. Nell'elenco Tipo di backup selezionare un tipo di backup. Il valore predefinito è Full.

   Importante

   È necessario eseguire almeno un backup completo del database prima di poter eseguire un backup differenziale o del log delle transazioni.

6. In Componente di cui eseguire il backup selezionare Database.

7. Nella sezione Destinazione esaminare il percorso predefinito per il file di backup (nella cartella ../mssql/data).

   Usa la lista Backup to per selezionare un dispositivo diverso. Seleziona Aggiungi per aggiungere oggetti di backup o destinazioni. Puoi distribuire il 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 esistente, selezionalo e seleziona Contenuti.

8. Opzionalmente, controlla le altre impostazioni nelle pagine Opzioni Media e Opzioni di Backup .

   Per maggiori informazioni sulle opzioni di backup, consulta Backup Database (Pagina Generale),Backup Database (Pagina Opzioni Media) e Backup Database (Pagina Opzioni Backup).

9. Selezionare OK per avviare il backup.

10. Quando il backup si completa con successo, seleziona OK per chiudere la finestra di dialogo.

Osservazioni:

• Quando specifichi un compito di backup in SQL Server Management Studio, puoi generare lo script Transact-SQL BACKUP corrispondente selezionando il pulsante Script e poi selezionando una destinazione di script.

• Dopo aver creato un backup completo del database, puoi creare un backup differenziale o un backup del log delle transazioni.

• Facoltativamente, seleziona la casella di controllo Backup di sola copia per creare un backup di sola copia. Un backup di sola copia è un backup di SQL Server indipendente dalla sequenza di backup convenzionali di SQL Server. Per altre informazioni, vedere Backup di sola copia. I backup di sola copia non sono disponibili per il tipo di backup Differential.

• L'opzione Sovrascrivi media è disabilitata nella pagina Opzioni Media quando fai il backup a un URL.

Esempi

Per i seguenti esempi, crea un database di test utilizzando il seguente codice Transact-SQL:

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 DEFAULT getdate() NOT NULL
);
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

Un. Backup completo su disco nel percorso predefinito

Questo esempio fa il backup del SQLTestDB database su disco nella posizione di backup predefinita.

1. In Esplora oggetti, collegati al motore di database e poi espandi l'albero server.

2. Espandere i database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività, quindi selezionare Backup....

3. Seleziona OK.

4. Quando il backup si completa con successo, seleziona OK per chiudere la finestra di dialogo.

B. Backup completo su disco in una posizione non predefinita

Questo esempio fa il backup del SQLTestDB database su disco in una posizione che scegli tu.

1. In Esplora oggetti, collegati al motore di database e poi espandi l'albero server.

2. Espandere i database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività, quindi selezionare Backup....

3. Nella pagina Generale, nella sezione Destinazione, seleziona Disco nell'elenco Esegui backup in.

4. Selezionare Rimuovi fino a quando non vengono rimossi tutti i file di backup esistenti.

5. Seleziona Aggiungi. Si apre la finestra di dialogo Seleziona Destinazione di Backup .

6. Immettere un percorso e un nome file validi nella casella Nome file . Usa .bak come estensione per semplificare la classificazione dei file.

7. Seleziona OK, poi seleziona di nuovo OK per iniziare il backup.

8. Quando il backup si completa con successo, seleziona OK per chiudere la finestra di dialogo.

C. Creare un backup crittografato

Questo esempio fa il backup del SQLTestDB database con la crittografia nella posizione di backup predefinita.

1. In Esplora oggetti, collegati al motore di database e poi espandi l'albero server.

2. Espandi Database, espandi Database di sistema, fai clic con il pulsante destro del mouse su master e quindi seleziona Nuova query per aprire una finestra di query connessa al database SQLTestDB.

3. Esegui i seguenti comandi per creare una chiave maestra del database e un certificato nel master database.

   -- Create the master key.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
   
   -- 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 = '<password>';
   
   -- Create the certificate encrypted by the master key.
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
   

4. In Esplora oggetti, nel nodo Database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività e quindi selezionare Backup.

5. Nella pagina Opzioni multimediali, nella sezione Sovrascrivi supporto, selezionare Esegui il backup in un nuovo set di supporti e cancella tutti i set di backup esistenti.

6. Nella pagina Opzioni di backup, nella sezione Crittografia, seleziona Crittografa backup.

7. Nell'elenco Algoritmo selezionare AES 256.

8. Nell'elenco Certificato o Chiave asimmetrica selezionare MyCertificate.

9. Seleziona OK.

D. Esegui il backup su Archiviazione BLOB di Azure

Questo esempio crea un backup completo del database di SQLTestDB in Archiviazione BLOB di Azure. L'esempio presuppone che tu abbia già un account storage con un contenitore blob. L'esempio crea una firma di accesso condivisa e fallisce se il container ha una firma di accesso condivisa esistente.

Se non hai un container gestione rete virtuale di Azure in un account storage, creane uno prima di continuare. Vedere Creare un account di archiviazione per utilizzo generico e Creare un contenitore.

1. In Esplora oggetti, collegati al motore di database e poi espandi l'albero server.

2. Espandere i database, fare clic con il pulsante destro del mouse su SQLTestDB, scegliere Attività, quindi selezionare Backup....

3. Nella pagina Generale, nella sezione Destinazione, seleziona URL nell'elenco Esegui il backup in.

4. Seleziona Aggiungi. Si apre la finestra di dialogo Seleziona Destinazione di Backup .

5. Se in precedenza hai registrato il container di storage Azure che vuoi usare con SSMS, selezionalo. In caso contrario, selezionare Nuovo contenitore per registrare un nuovo contenitore.

6. Nella finestra di dialogo Collegati a un abbonamento Microsoft, accedi al tuo account.

7. Nella casella Seleziona account di archiviazione selezionare l'account di archiviazione.

8. Nella casella Seleziona contenitore Blob, seleziona il contenitore Blob.

9. Nella casella del calendario Scadenza della Policy di Accesso Condiviso , seleziona una data di scadenza per la policy di accesso condiviso che questo esempio crea.

10. Seleziona Crea Credential per generare una firma di accesso condiviso e una credenziale in SSMS.

11. Seleziona OK per chiudere la finestra di dialogo Collegati a un abbonamento Microsoft.

12. Nella casella File di backup modificare il nome del file di backup se si desidera.

13. Seleziona OK per chiudere la finestra di dialogo Seleziona una destinazione di backup .

14. Selezionare OK per avviare il backup.

15. Quando il backup si completa con successo, seleziona OK per chiudere la finestra di dialogo.

Usare Transact-SQL

Crea un backup completo del database eseguendo l'istruzione BACKUP DATABASE . Specificare:

• 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 <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];

Facoltativamente, specificare una o più WITH opzioni. Di seguito sono descritte alcune opzioni di base WITH . Per informazioni su tutte le WITH opzioni, vedi BACKUP.

Opzioni del set di backup di WITH base:

• { COMPRESSIONE | NESSUNA_COMPRESSIONE }. SQL Server specifica se la compressione di backup viene eseguita sul backup, sovrascrivendo il valore predefinito a livello di server.

• CRITTOGRAFIA (ALGORITMO, SERVER ASYMMETRIC KEY | CERTIFICATE). In SQL Server 2014 o successivamente, specifica l'algoritmo di crittografia da utilizzare e il certificato o la chiave asimmetrica da utilizzare per proteggere la crittografia.

• DESCRIZIONE = { 'testo' | @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 non specifichi NAME, è vuoto.

Per impostazione predefinita, BACKUP accoda il backup a un set di supporti esistente, conservando i set di backup esistenti. Per specificare in modo esplicito questa configurazione, usare l'opzione NOINIT . Per informazioni sull'aggiunta a un set di backup esistente, vedere Set di supporti, famiglie di supporti e set di backup (SQL Server).

Per formattare il supporto di backup, usare l'opzione FORMAT :

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { testo | @text_variable } ]

Usa la FORMAT clausola quando usi i media per la prima volta, o quando vuoi sovrascrivere tutti i dati esistenti. Facoltativamente, assegnare al nuovo supporto un nome e una descrizione.

Esempi

Per i seguenti esempi, crea un database di test utilizzando il seguente codice Transact-SQL:

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 DEFAULT GETDATE() NOT NULL
);
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

Un. Effettuare un backup su un'unità disco

Nell'esempio seguente viene eseguito il backup del database completo SQLTestDB su disco. Usa 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 del database completo SQLTestDB su nastro. Aggiunge il backup ai backup precedenti.

USE SQLTestDB;
GO

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

C. Effettua il backup su un dispositivo logico a nastro

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

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO

EXECUTE 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

PowerShell

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

Se apri una finestra PowerShell da SSMS per collegarti al Motore di database di SQL Server, puoi omettere la parte delle credenziali perché la tua credenziali in SSMS stabilisce automaticamente la connessione tra PowerShell e motore di database.

Esempi

Un. 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 completi, vedere Backup-SqlDatabase.

$credential = Get-Credential

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

B. Backup completo in Azure

L'esempio seguente crea un backup completo del database <myDatabase> sull'istanza <myServer> nell'archiviazione BLOB. Viene creata una politica di accesso memorizzato con diritti di lettura, scrittura e elenco. La credenziale di SQL Server, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, viene creata utilizzando una firma di accesso condivisa associata alla stored access policy. Il comando usa il $backupFile parametro 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

Attività correlate

• Creare un backup differenziale del database (SQL Server)
• Ripristinare un backup del database con SSMS
• Ripristina un backup del database con il modello di recupero semplice
• Ripristino del database fino al punto del guasto - ripristino completo
• Ripristinare un database in una nuova posizione (SQL Server)
• Usare la Creazione guidata Piano di Manutenzione

Contenuto correlato

• Risolvere i problemi relativi alle operazioni di backup e ripristino di SQL Server
• Panoramica del backup (SQL Server)
• Backup del log delle transazioni (SQL Server)
• Set di supporti, gruppi di supporti e set di backup (SQL Server)
• sys.sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• Backup database (pagina Generale)
• Backup database (pagina Opzioni di backup)
• Backup differenziali (SQL Server)
• Backup completi del database (SQL Server)