Risolvere i problemi nelle operazioni di backup e ripristino di SQL Server

Versione originale del prodotto: SQL Server
Numero KB originale: 224071

Sommario

Questo articolo illustra come risolvere i problemi comuni relativi alle operazioni di backup e ripristino SQL Server. Questi problemi includono prestazioni di backup o ripristino lente, errori di compatibilità delle versioni, processi di backup del gruppo di disponibilità AlwaysOn, errori multimediali, errori di autorizzazioni, backup VDI e VSS di terze parti, errori di rilevamento delle modifiche e ripristini del database crittografati. L'articolo include anche una sezione delle domande frequenti e collegamenti ad argomenti di riferimento per SQL Server backup e ripristino.

Le operazioni di backup e ripristino richiedono molto tempo

Le operazioni di backup e ripristino richiedono un numero elevato di operazioni di I/O. La velocità effettiva di backup e ripristino dipende dal livello di ottimizzazione del sottosistema di I/O sottostante per gestire il volume di I/O. Se si sospetta che le operazioni di backup vengano arrestate o che il completamento delle operazioni di backup richiede troppo tempo, usare uno o più dei metodi seguenti per stimare il tempo di completamento o per tenere traccia dello stato di avanzamento di un'operazione di backup o ripristino:

• Il log degli errori di SQL Server contiene informazioni sulle operazioni di backup e ripristino precedenti. È possibile usare questi dettagli per stimare il tempo necessario per eseguire il backup e il ripristino del database nello stato corrente. Di seguito è riportato un output di esempio del log degli errori:

  RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec)
  

• In SQL Server 2016 e versioni successive usare XEvent backup_restore_progress_trace per tenere traccia dello stato di avanzamento delle operazioni di backup e ripristino.

• Usare la colonna percent_complete di sys.dm_exec_requests per tenere traccia dello stato di avanzamento delle operazioni di backup e ripristino in corso.

• Misurare la velocità effettiva di backup e ripristino usando i contatori di monitoraggio delle prestazioni Device throughput Bytes/sec e Backup/Restore throughput/sec. Per altre informazioni, vedere SQL Server, Backup Device Object.For more information, see SQL Server, Backup Device Object.

• Usare lo script estimate_backup_restore per ottenere una stima dei tempi di backup.

• Consulta Come funziona: che cosa fa il ripristino/backup?. Questo post di blog fornisce informazioni dettagliate sulla fase corrente delle operazioni di backup o ripristino.

Analizzare le prestazioni di backup o ripristino lente

1. Controllare se si verifica uno dei problemi noti nella tabella seguente e valutare la possibilità di applicare le correzioni o le procedure consigliate pertinenti.

   Collegamento alla Knowledge Base	Spiegazione e azioni consigliate
   Backup e ripristino di database di SQL Server	Vengono illustrate le procedure consigliate che consentono di migliorare le prestazioni di backup e ripristino. Ad esempio, concedere il privilegio SE_MANAGE_VOLUME_NAME all'account Windows che esegue SQL Server per consentire l'inizializzazione immediata dei file velocizza le operazioni sui file di dati.
   Configurare il software antivirus per l'uso con SQL Server	Il software antivirus potrebbe contenere blocchi sui .bak file, che possono influire sulle prestazioni delle operazioni di backup e ripristino. Seguire le indicazioni riportate in questo articolo per escludere i file di backup dalle analisi antivirus.
   Un'operazione di backup o ripristino in un percorso di rete è lenta	Isolare il problema nella rete copiando un file di dimensioni simili nel percorso di rete dal server che esegue SQL Server e controllare le prestazioni.

2. Controllare il log degli errori di SQL Server e il registro eventi di Windows alla ricerca di messaggi di errore che indichino la causa del problema.

3. Se si usano piani di manutenzione di database o software di terze parti per eseguire backup simultanei, è consigliabile modificare le pianificazioni per ridurre al minimo i conflitti nell'unità in cui vengono scritti i backup.

4. Collaborare con l'amministratore di Windows per verificare la disponibilità di aggiornamenti del firmware per l'hardware.

Errori durante il ripristino dei backup nelle versioni precedenti di SQL Server

Symptoms

Non è possibile ripristinare un backup SQL Server in una versione precedente di SQL Server rispetto alla versione che ha creato il backup. Ad esempio, non è possibile ripristinare un backup eseguito in un'istanza di SQL Server 2022 in un'istanza di SQL Server 2019. In caso contrario, viene visualizzato il messaggio di errore seguente:

Errore 3169: è stato eseguito il backup del database in un server che esegue la versione %ls. Tale versione è incompatibile con il server, che esegue la versione %ls. Ripristinare il database in un server che supporta il backup oppure utilizzare un backup compatibile con questo server.

Resolution

Utilizzare il metodo seguente per copiare un database ospitato in una versione successiva di SQL Server in una versione precedente di SQL Server.

1. Scaricare e installare la versione più recente di SQL Server Management Studio (SSMS) sia in SQL_A che in SQL_B.
2. In SQL_A seguire questa procedura:
   1. Fare clic con il pulsante destro del mouse su <Attività>>>Genera script e selezionare l'opzione per creare script per l'intero database e tutti gli oggetti di database.
   2. Nella schermata Imposta opzioni di scripting, selezionare Avanzate, quindi selezionare la versione di SQL_B in Generale>Script per la versione di SQL Server. Selezionare quindi l'opzione di salvataggio più adatta per l'utente e continuare la procedura guidata.
   3. Usare l'utilità di programma di copia bulk (bcp) per copiare dati da tabelle diverse.
3. In SQL_B seguire questa procedura:
   1. Usare gli script generati nel server SQL_A per creare lo schema del database.
   2. In ogni tabella disabilitare eventuali vincoli di chiave esterna e trigger. Se la tabella contiene colonne Identity, abilitare identity insert.
   3. Usare bcp per importare i dati esportati nel passaggio precedente nelle tabelle corrispondenti.
   4. Al termine dell'importazione dei dati, abilitare i vincoli e i trigger di chiave esterna e disabilitare l'inserimento di identità per ognuna delle tabelle modificate nel passaggio c.

Questa procedura funziona in genere correttamente per i database di piccole e medie dimensioni. Per i database di dimensioni maggiori, potrebbero verificarsi problemi di memoria insufficiente in SSMS e in altri strumenti. È consigliabile usare SQL Server Integration Services (SSIS), replica o altre opzioni per copiare un database da una versione successiva a una versione precedente di SQL Server.

Per altre informazioni su come generare script per il database, vedere Creare lo script di un database usando l'opzione Genera script.

Problemi del processo di backup nei gruppi di disponibilità AlwaysOn

Symptoms

Si verificano problemi che influiscono sui processi di backup o sui piani di manutenzione negli ambienti del gruppo di disponibilità AlwaysOn.

Resolution

• Per impostazione predefinita, la preferenza per il backup automatico è impostata su Prefer Secondary .By default, the automatic backup preference is set to Prefer Secondary. Questa impostazione specifica che i backup vengono eseguiti in una replica secondaria, a meno che la replica primaria non sia l'unica replica online. Non è possibile eseguire backup differenziali del database con questa impostazione. Per modificare questa impostazione, usare SSMS nella replica primaria corrente e passare alla pagina Preferenze di backup in Proprietà del gruppo di disponibilità.
• Se si usa un piano di manutenzione o processi pianificati per generare backup dei database, creare i processi per ogni database di disponibilità in ogni istanza del server che ospita una replica di disponibilità per il gruppo di disponibilità.

Per altre informazioni sui backup in un ambiente AlwaysOn, vedere gli articoli seguenti:

• Configurare i backup sulle repliche secondarie di un gruppo di disponibilità Always On
• Scaricare i backup supportati alle repliche secondarie di un gruppo di disponibilità

Errori relativi ai supporti durante il ripristino di un database da un backup

Symptoms

Messaggi di errore che indicano un problema di file in genere puntano a un file di backup danneggiato. Gli errori seguenti sono esempi di problemi che possono verificarsi se un set di backup è danneggiato:

3241: La famiglia di supporti sul dispositivo '%ls' non è formata correttamente. SQL Server non è in grado di elaborare questa famiglia di supporti.

3242: Il file nel dispositivo '%ls' non è un set di backup valido nel formato Microsoft Tape Format.

3243: "The media family on device '%ls' was created using Microsoft Tape Format version %d.%d" (Il gruppo di supporti nel dispositivo "%l" è stato creato usando la versione %d.%d di Microsoft Tape Format). SQL Server supporta la versione %d.%d.

Motivo

Questi problemi possono verificarsi a causa di problemi che influiscono sull'hardware sottostante (dischi rigidi, archiviazione di rete e così via) o a causa di un virus o malware. Esaminare i registri eventi di sistema Windows e i log hardware per individuare gli errori segnalati e intervenire in modo appropriato ( ad esempio, aggiornare il firmware o risolvere i problemi di rete).

Resolution

• Usare l'istruzione RESTORE HEADERONLY per controllare il backup.
• Per ridurre l'occorrenza di questi errori di ripristino, abilitare l'opzione CHECKSUM di backup quando si esegue un backup per evitare il backup di un database danneggiato. Per altre informazioni, vedere Possibili errori dei supporti durante il backup e il ripristino (SQL Server).
• È anche possibile abilitare il flag di traccia 3023 per abilitare un checksum quando si eseguono i backup usando gli strumenti di backup. Per ulteriori informazioni, vedi Configurazione del server: checksum di backup predefinito.
• Per risolvere questi problemi, individuare un altro file di backup utilizzabile o creare un nuovo set di backup. Microsoft non offre soluzioni che consentono di recuperare i dati da un set di backup danneggiato.
• Se un file di backup viene ripristinato correttamente in un server ma non in un altro, provare modi diversi per copiare il file tra i server. Ad esempio, provare robocopy anziché un'operazione di copia regolare. Verificare se il file viene modificato durante l'operazione di copia nella rete o nel dispositivo di archiviazione di destinazione.

I backup hanno esito negativo a causa di problemi di autorizzazioni

Symptoms

Quando si tenta di eseguire operazioni di backup del database, si verifica uno degli errori seguenti.

• Scenario 1: quando si esegue un backup da SQL Server Management Studio, il backup ha esito negativo e restituisce il messaggio di errore seguente:

  Backup non riuscito per il server <Nome server>. (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: impossibile aprire il dispositivo di backup '<device name>'. Errore del sistema operativo 5(Accesso negato). (Microsoft.SqlServer.Smo)

• Scenario 2: i backup pianificati hanno esito negativo e generano un messaggio di errore registrato nella cronologia del processo non riuscito e simile al seguente:

  Executed as user: <Owner of the job>. ....2 for 64-bit  Copyright (C) 2019 Microsoft. All rights reserved.    
  Started:  5:49:14 PM  Progress: 2021-08-16 17:49:15.47    
  Source: {GUID}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  
  Error: 2021-08-16 17:49:15.74     
  Code: 0xC002F210     
  Source: Back Up Database (Full) Execute SQL Task     
  Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'C:\backups\D..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". 
  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
  

Motivo

Entrambi gli scenari possono verificarsi se l'account del servizio SQL Server non dispone delle autorizzazioni lettura e scrittura per la cartella in cui vengono scritti i backup. Le istruzioni di backup possono essere eseguite come parte di un passaggio del processo o manualmente da SQL Server Management Studio. In entrambi i casi, vengono eseguiti nel contesto dell'account di avvio del servizio SQL Server. Pertanto, se l'account del servizio non dispone dei privilegi necessari, vengono visualizzati i messaggi di errore annotati in precedenza.

Resolution

Controllare le autorizzazioni correnti dell'account del servizio SQL Server in una cartella passando alla scheda Security nelle proprietà della cartella, selezionando Advanced e quindi usando la scheda Active Access. Per altre informazioni, vedere dispositivi Backup.

Le operazioni di backup o ripristino di terze parti hanno esito negativo

SQL Server fornisce un'interfaccia VDI (Virtual Backup Device Interface). Questa API consente ai fornitori di software indipendenti di integrare SQL Server nei propri prodotti per supportare le operazioni di backup e ripristino. Queste API sono progettate per offrire affidabilità e prestazioni e per supportare l'intera gamma di funzionalità di backup e ripristino di SQL Server, incluse le funzionalità di snapshot e backup a caldo.

Normale procedura di risoluzione dei problemi

• In tutte le versioni supportate di SQL Server viene creato e sottoposto a provisioning un account di accesso denominato NT SERVICE\SQLWriter durante l'installazione. Verificare che l'account di accesso esista in SQL Server e faccia parte del ruolo del server sysadmin nell'istanza di di cui viene eseguito il backup. Verificare inoltre che il servizio VSS Writer SQL Server sia avviato e che l'account di avvio sia impostato su Local System.

• Verificare che SqlServerWriter sia elencato quando si esegue il comando VSSADMIN LIST WRITERS al prompt dei comandi con privilegi elevati nel server che esegue SQL Server. Il writer deve essere presente e in stato Stabile affinché i backup VSS vengano completati correttamente.

• Per altre informazioni, controllare i log dal software di backup e dal sito di supporto del fornitore.

  Sintomi o scenari	Riferimenti
  Informazioni sul funzionamento del backup VDI	Come funziona: SQL Server - Risorse di backup VDI (VSS)
  Numero di database di cui è possibile eseguire il backup contemporaneamente	Funzionamento: numero di database di cui è possibile eseguire il backup contemporaneamente?

I backup hanno esito negativo quando il rilevamento delle modifiche è abilitato

Symptoms

È possibile che i backup non riescano quando si abilita il rilevamento delle modifiche nel database. Potrebbe essere visualizzato un errore simile al seguente:

Errore: 3999, gravità: 17, stato: 1.
<Time Stamp> spid <spid> Impossibile scrivere la tabella di commit su disco in dbid 8 a causa dell'errore 2601. Per ulteriori informazioni controllare il log degli errori.

Resolution

Se si verifica questo problema in una versione supportata di SQL Server, installare l'aggiornamento cumulativo più recente per la versione. Per le correzioni in background e cronologiche, vedere gli articoli seguenti:

• 2682488 FIX: l'operazione di backup non riesce in sql Server 2008, in sql Server 2008 R2 o in un database di SQL Server 2012 dopo aver abilitato il rilevamento delle modifiche

Errori durante il ripristino dei backup dei database crittografati

Symptoms

Si verificano problemi quando si ripristinano i backup dei database protetti da Transparent Data Encryption (TDE).

Resolution

Per risolvere il problema, vedere Spostare un database protetto da TDE in un'altra istanza di SQL Server.

Domande frequenti sul backup e il ripristino di SQL Server

Come è possibile controllare lo stato di un'operazione di backup?

Usare lo script estimate_backup_restore per stimare i tempi di backup.

Cosa devo fare se si verifica un failover di SQL Server durante un backup?

Riavviare l'operazione di ripristino o di backup come descritto in Riavviare un'operazione di ripristino interrotta (Transact-SQL).

È possibile ripristinare i backup del database da versioni precedenti nelle versioni più recenti e viceversa?

Non è possibile ripristinare un backup SQL Server usando una versione di SQL Server precedente alla versione che ha creato il backup. Per altre informazioni, vedere Supporto per la compatibilità RESTORE.

Come è possibile controllare i backup del database SQL Server?

Vedere le procedure documentate in istruzioni RESTORE - VERIFYONLY (Transact-SQL).

Come è possibile ottenere la cronologia di backup dei database in SQL Server?

Vedere Come ottenere la cronologia di backup dei database in SQL Server.

È possibile ripristinare i backup a 32 bit su server a 64 bit e viceversa?

Sì. Il formato di archiviazione su disco SQL Server è lo stesso in ambienti a 64 bit e a 32 bit. Le operazioni di backup e ripristino funzionano quindi in ambienti a 64 bit e a 32 bit.

Come si esegue il backup e il ripristino di un database protetto da Transparent Data Encryption (TDE)?

Eseguire il backup del database, il certificato server della chiave di crittografia del database e la chiave privata del certificato. Per ripristinare il backup in un'altra istanza, ripristinare prima di tutto il certificato del server (con la relativa chiave privata) nel master database nell'istanza di destinazione e quindi ripristinare il backup del database utente. Per istruzioni dettagliate, vedere Spostare un database protetto da TDE in un altro SQL Server.

La compressione dei backup funziona nei database abilitati per TDE?

Sì. A partire da SQL Server 2016, la compressione dei backup funziona nei database abilitati per TDE quando si specifica MAXTRANSFERSIZE maggiore di 65536 (64 KB) nell'istruzione BACKUP. Senza questa impostazione, il backup viene eseguito non compresso anche quando si richiede la compressione. Per informazioni dettagliate, vedere Compressione dei backup.

In che modo i backup di VDI e VSS interagiscono con le repliche secondarie del gruppo di disponibilità AlwaysOn?

I backup basati su VSS (snapshot) eseguiti tramite il servizio Writer SQL sono supportati solo sulla replica primaria. Nelle repliche secondarie richiedere un backup completo di sola copia tramite il client VDI, perché i backup completi di VsS in un database secondario non sono supportati. Per altre informazioni, vedere Repliche secondarie attive: Backup in repliche secondarie (gruppi di disponibilità AlwaysOn).

Suggerimenti generali sulla risoluzione dei problemi

• Concedi le autorizzazioni di lettura e scrittura all'account di servizio di SQL Server nella cartella in cui vengono scritti i backup. Per altre informazioni, vedere Autorizzazioni per il backup.
• Verificare che la cartella in cui si scrivono i backup disponga di spazio sufficiente per i backup del database. Utilizzare la sp_spaceused stored procedure per ottenere una stima approssimativa delle dimensioni del backup per un database.
• Usare la versione più recente di SSMS per evitare problemi noti correlati alla configurazione del piano di manutenzione e del processo.
• Eseguire un'esecuzione di test dei processi per verificare che i backup vengano creati correttamente. Aggiungi la logica per verificare i backup.
• Se si prevede di spostare i database di sistema da un server a un altro, vedere Spostare i database di sistema.
• Se vengono visualizzati errori di backup intermittenti, verificare se l'aggiornamento più recente per la versione SQL Server risolve il problema. Per altre informazioni, vedere SQL Server versioni e aggiornamenti.
• Per pianificare e automatizzare i backup per SQL Server edizioni Express, vedere Schedule e automatizzare i backup dei database SQL Server in SQL Server Express.

Argomenti di riferimento per SQL Server backup e ripristino

Nella tabella seguente sono elencati gli argomenti da esaminare per attività di backup e ripristino specifiche.

Contenuti correlati

• Backup e ripristino di database di SQL Server
• Autorizzazioni per il backup
• Possibili errori relativi ai supporti durante il backup e il ripristino (SQL Server)
• Spostare un database protetto da TDE in un'altra istanza di SQL Server