Creazione di un backup completo del database

Si applica a: sìSQL Server (tutte le versioni supportate)

In questo argomento viene descritto come creare un backup completo del database in SQL Server usando SQL Server Management Studio, Transact-SQLo PowerShell.

Per altre informazioni, vedere Eseguire SQL Server backup e ripristino con il servizio BLOB di Azure Archiviazione e SQL Server backup nell'URL.

Limitazioni e restrizioni

  • BACKUPL'istruzione non è consentita 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.

Indicazioni

  • 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 si esegue spesso il backup, i messaggi di operazione riuscita si accumulano rapidamente, con la conseguente creazione di log degli errori di grandi quantità, rendendo difficile la ricerca 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 SQL Server 2012 (11.x) da , 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 SQL Server servizio deve leggere e scrivere nel dispositivo. L'account con cui viene SQL Server eseguito il servizio deve disporre delle autorizzazioni di scrittura per il dispositivo di backup. Tuttavia, sp_addumpdevice, che aggiunge una voce per un dispositivo di backup nelle tabelle di sistema, non controlla le autorizzazioni di accesso ai file. I problemi nel file fisico del dispositivo di backup potrebbero non essere visualizzati fino a quando non viene usato il backup o non viene eseguito un 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 selezionando una destinazione per lo script.

  1. Dopo essersi connessi all'istanza appropriata del Motore di database di SQL Server di Microsoft, in Esplora oggetti espandere l'albero del server.

  2. Espandere Database e selezionare un database utente o espandere Database di sistema e selezionare un database di sistema.

  3. Fare clic con il pulsante destro del mouse sul database di cui si vuole eseguire il backup, scegliere Attività e quindi selezionare Backup....

  4. 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.

  5. Nell'elenco a discesa Tipo di backup selezionare un 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.

  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).

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

    Per rimuovere una destinazione di backup, selezionarla e selezionare Rimuovi. Per visualizzare il contenuto di una destinazione di backup esistente, selezionarla e selezionare Contenuto.

  8. 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.

  9. Selezionare OK per avviare il backup.

  10. Al termine del backup, selezionare OK per chiudere la finestra SQL Server Management Studio backup.

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 di backup convenzionali di SQL Server. Per altre informazioni, vedere Backup di sola copia (SQL Server). Non è disponibile un backup di sola copia per il tipo di backup differenziale.

  • L'opzione Sovrascrivi supporti è disabilitata nella pagina Opzioni supporti se si sta eseguire il backup in 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.

  1. Dopo essersi connessi all'istanza appropriata del Motore di database di SQL Server di Microsoft, in Esplora oggetti espandere l'albero del server.

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

  3. Selezionare OK.

  4. Al termine del backup, selezionare OK per chiudere la finestra SQL Server Management Studio backup.

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.

  1. Dopo essersi connessi all'istanza appropriata del Motore di database di SQL Server di Microsoft, in Esplora oggetti espandere l'albero del server.

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

  3. Nella sezione Destinazione della pagina Generale selezionare Disco dall'elenco a discesa Backup su: .

  4. Selezionare Rimuovi finché non vengono rimossi tutti i file di backup esistenti.

  5. Selezionare Aggiungi. Verrà aperta la finestra di dialogo Selezionare la destinazione di backup.

  6. 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.

  7. Selezionare OK e quindi di nuovo OK per avviare il backup.

  8. Al termine del backup, selezionare OK per chiudere la finestra SQL Server Management Studio backup.

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.

  1. Dopo essersi connessi all'istanza appropriata del Motore di database di SQL Server di Microsoft, in Esplora oggetti espandere l'albero del server.

  2. Espandere Database, Espandere Database di sistema, fare clic con il pulsante destro del mouse su e selezionare Nuova query per aprire una finestra di query con una connessione al master SQLTestDB database.

  3. 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';  
    
  4. In Esplora oggetti, nel nodo Database fare clic con il pulsante destro del mouse su , scegliere Attività e quindi SQLTestDB selezionare Backup... .

  5. 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.

  6. Nella sezione Crittografia della pagina Opzioni di backup selezionare la casella di controllo Crittografa backup .

  7. Nell'elenco a discesa Algoritmo selezionare AES 256.

  8. Nell'elenco a discesa Certificato o chiave asimmetrica selezionare MyCertificate.

  9. Selezionare OK.

Backup crittografato

D. Backup nel servizio Archiviazione BLOB di Azure

L'esempio crea un backup completo del database SQLTestDB di nel servizio di archiviazione BLOB di Azure. Nell'esempio si presuppone che si abbia già un account di archiviazione con un contenitore BLOB. Nell'esempio viene creata automaticamente una firma di accesso condiviso. Questo esempio ha esito negativo se il contenitore ha una firma di accesso condiviso esistente.

Se non si ha un contenitore 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.

  1. Dopo essersi connessi all'istanza appropriata del Motore di database di SQL Server di Microsoft, in Esplora oggetti espandere l'albero del server.

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

  3. Nella pagina Generale nella sezione Destinazione selezionare l' URL dall'elenco a discesa Backup in: .

  4. Selezionare Aggiungi. Verrà aperta la finestra di dialogo Selezionare la destinazione di backup.

  5. 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.

  6. Nella finestra di dialogo Connetti a una sottoscrizione Microsoft accedere al proprio account.

  7. Nella casella di testo dell'elenco a discesa Seleziona account di archiviazione selezionare l'account di archiviazione.

  8. Nella casella di testo dell'elenco a discesa Seleziona contenitore BLOB selezionare il contenitore BLOB.

  9. 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.

  10. Selezionare Crea credenziali per generare una firma e una credenziale di accesso condiviso SQL Server Management Studio.

  11. Selezionare OK per chiudere la Connessione a una sottoscrizione Microsoft.

  12. Nella casella di testo File di backup modificare il nome del file di backup (facoltativo).

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

  14. Selezionare OK per avviare il backup.

  15. Al termine del backup, selezionare OK per chiudere la SQL Server Management Studio dialogo.

Uso 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 } = nome_dispositivo_fisico_backup

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 Enterprise Edition e versioni successive, specifica se la compressione backup è eseguita su 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 NAME non viene specificato, è 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, usare NOINIT l'opzione . Per informazioni sull'accodamento a set di backup esistenti, vedere Set di supporti, gruppi di supporti e set di backup (SQL Server).

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

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

Usare la clausola FORMAT quando si usano supporti 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 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 eseguire Install-Module -Name SqlServer l'installazione, eseguire in una sessione amministratore di PowerShell.

Per altre informazioni, vedere Provider PowerShell per SQL Server.

Importante

Se si apre una finestra di PowerShell dall'interno di SQL Server Management Studio per connettersi a un'installazione di SQL Server, è possibile omettere la parte relativa alle credenziali perché le credenziali in SSMS vengono usate automaticamente 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 gli 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

Nell'esempio seguente viene creato un backup completo del database <myDatabase> sull'istanza <myServer> per il servizio 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

Vedere anche