Creare un backup completo del database
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
BACKUPin 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.
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.
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
mastere scegliere Nuova query per aprire una finestra di query con una connessione al databaseSQLTestDB.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.
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.
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
Utilizzo di 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
Attività correlate
- Eseguire il backup di un database (SQL Server)
- Creare un backup differenziale del database (SQL Server)
- Ripristinare un backup del database con SSMS
- Ripristinare un backup del database nel modello di recupero con registrazione minima (Transact-SQL)
- Ripristinare un database al punto di errore nel modello di recupero con registrazione completa (Transact-SQL)
- Ripristino di un database in una nuova posizione (SQL Server)
- Usare la Creazione guidata piano di manutenzione
Contenuto correlato
- Risoluzione dei problemi di backup in SQL Server e operazioni di ripristino
- Panoramica del backup (SQL Server)
- Backup del log delle transazioni (SQL Server)
- Set di supporti, gruppi di supporti e set 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)
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per