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

Questo articolo fornisce soluzioni per problemi comuni che possono verificarsi durante le operazioni di backup e ripristino di Microsoft SQL Server e fornisce riferimenti a ulteriori informazioni su queste operazioni.

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

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 del backup/del ripristino dipende da quanto il sottosistema di I/O sottostante è ottimizzato per gestire il volume delle operazioni di I/O. Se si sospetta che le operazioni di backup vengano arrestate o che il completamento richiede troppo tempo, è possibile 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 è possibile usare XEvent backup_restore_progress_trace per tenere traccia dello stato di avanzamento delle operazioni di backup e ripristino.

• È possibile usare la percent_complete colonna di sys.dm_exec_requests per tenere traccia dello stato di avanzamento delle operazioni di backup e ripristino in anteprima.

• È possibile misurare le informazioni sulla velocità effettiva di backup e ripristino usando i Device throughput Bytes/sec contatori di monitoraggio delle prestazioni 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.

• Fare riferimento a Come funziona: Operazioni di ripristino/backup?. Questo post di blog fornisce informazioni dettagliate sulla fase corrente delle operazioni di backup o ripristino.

Verifiche necessarie

1. Verificare se si verificano problemi noti elencati nella tabella seguente. Valutare se implementare le modifiche o applicare le correzioni e le procedure consigliate descritte negli articoli corrispondenti.

   Collegamento alla Knowledge Base o alla documentazione online	Spiegazione e azioni consigliate
   Ottimizzazione delle prestazioni di backup e ripristino in SQL Server	L'argomento della documentazione online illustra varie procedure consigliate che è possibile usare per migliorare le prestazioni delle operazioni di backup/ripristino. Ad esempio, è possibile assegnare il privilegio speciale all'account di Windows che esegue SQL Server per abilitare l'inizializzazione SE_MANAGE_VOLUME_NAME immediata dei file di dati. Ciò può produrre miglioramenti significativi in termini di prestazioni.
   2920151 Hotfix e aggiornamenti consigliati per i cluster di failover basati su Windows Server 2012 R2 2822241 aggiornamento cumulativo di Windows 8 e Windows Server 2012: aprile 2013	Gli aggiornamenti cumulativi correnti del sistema possono includere correzioni per problemi noti a livello di sistema che possono compromettere le prestazioni dei programmi, ad esempio SQL Server. L'installazione di questi aggiornamenti può aiutare a prevenire tali problemi.
   2878182 FIX: i processi in modalità utente in un'applicazione non rispondono nei server che eseguono Windows Server 2012	Le operazioni di backup sono a elevato utilizzo di I/O e possono essere interessate da questo bug. Applicare questa correzione per evitare questi problemi.
   Configurare il software antivirus per l'uso con SQL Server	Il software antivirus può contenere blocchi sui file .bak. Ciò può 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.
   2820470 Messaggio di errore ritardato quando si tenta di accedere a una cartella condivisa che non esiste più in Windows	Illustra un problema che si verifica quando si tenta di accedere a una cartella condivisa che non esiste più in Windows 2012 e versioni successive.
   967351 Un file molto frammentato in un volume NTFS potrebbe non aumentare oltre una determinata dimensione	Illustra un problema che si verifica quando un file system NTFS è molto frammentato.
   304101 programma di backup non riesce quando si esegue il backup di un volume di sistema di grandi dimensioni
   2455009 CORREZIONE: rallentamento delle prestazioni quando si ripristina un database se nel log delle transazioni nel log delle transazioni in SQL Server 2005, in SQL Server 2008 o in SQL Server 2008 R2	La presenza di molti file di log virtuali potrebbe influire sul tempo necessario per ripristinare un database. Ciò vale soprattutto durante la fase di ripristino dell'operazione di ripristino. Per informazioni su altri possibili problemi che possono essere causati dalla presenza di molti file di log virtuali, vedere Il completamento delle operazioni del database richiede molto tempo oppure genera errori quando il log delle transazioni include numerosi file di log virtuali.
   Un'operazione di backup o ripristino in un percorso di rete è lenta	Isolare il problema nella rete provando a copiare un file di dimensioni simili nel percorso di rete dal server che esegue SQL Server. Verificare le prestazioni.

2. Verificare la presenza di messaggi di errore nel log degli errori di SQL Server e nel registro eventi di Windows per altri puntatori sulla causa del problema.

3. Se si usano software di terze parti o piani di manutenzione del database per eseguire backup simultanei, valutare se è necessario modificare le pianificazioni per ridurre al minimo la contesa 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.

Problemi che influiscono sul ripristino del database tra versioni diverse di SQL Server

Non è possibile ripristinare un backup di SQL Server in una versione precedente di SQL Server rispetto alla versione in cui è stato creato il backup. Ad esempio, non è possibile ripristinare un backup eseguito in un'istanza di SQL Server 2019 in un'istanza di SQL Server 2017. 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.

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

Note

La procedura seguente presuppone che siano presenti due istanze di SQL Server denominate SQL_A (versione successiva) e SQL_B (versione precedente).

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à>database>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 e quindi selezionare la versione di SQL_B in Script generale>per la versione di SQL Server. Selezionare anche l'opzione più adatta per salvare gli script generati. Continuare quindi 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 e trigger di chiavi esterne. 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 interessate nel passaggio c.

Questa procedura funziona in genere correttamente per i database di piccole e medie dimensioni. Per i database di dimensioni maggiori, i problemi di memoria insufficiente possono verificarsi in SSMS e in altri strumenti. È consigliabile usare SQL Server Integration Services (SSIS), la replica o altre opzioni per creare una copia di 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 in ambienti Always On

Se si verificano problemi che influiscono sui processi di backup o sui piani di manutenzione in ambienti Always On, tenere presente quanto segue:

• Per impostazione predefinita, la preferenza per il backup automatico è impostata su Prefer Secondary .By default, the automatic backup preference is set to Prefer Secondary. Specifica che i backup devono essere eseguiti in una replica secondaria, tranne se la replica primaria è l'unica replica online. Non è possibile eseguire backup differenziali del database usando 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, assicurarsi di 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 Always On, vedere gli argomenti seguenti:

• Configurare i backup nelle repliche secondarie di un gruppo di disponibilità AlwaysOn
• Ripartire i backup supportati nelle repliche secondarie di un gruppo di disponibilità

Errori correlati ai supporti quando si ripristina un database da un backup

Se vengono visualizzati messaggi di errore che indicano un problema di file, si tratta di un sintomo di un file di backup danneggiato. Di seguito sono riportati alcuni esempi di errori che è possibile ottenere se un set di backup è danneggiato:

• 3241: la famiglia di supporti nel dispositivo '%ls' non è corretta. Impossibile elaborarlo.

• 3242: il file nel dispositivo '%ls' non è un set di backup di Formato nastro Microsoft valido.

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

Note

È possibile usare l'istruzione Restore Header per controllare i backup.

Questi problemi possono verificarsi a causa di problemi che interessano l'hardware sottostante (dischi rigidi, archiviazione di rete e così via) o che sono correlati a 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).

Per evitare questi errori, 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 relativi ai 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 altre informazioni, vedere Come abilitare l'opzione CHECKSUM se le utilità di backup non espongono l'opzione .

Per risolvere questi problemi, è necessario 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.

Note

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.

I backup hanno esito negativo a causa di problemi di autorizzazioni

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

Uno di questi scenari può 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 di processo o manualmente da SQL Server Management Studio. In entrambi i casi, vengono sempre eseguiti nel contesto dell'account di avvio del servizio SQL Server. Pertanto, se l'account del servizio non dispone dei privilegi necessari, si ricevono i messaggi di errore annotati in precedenza.

Per altre informazioni, vedere Dispositivi di backup.

Note

È possibile controllare le autorizzazioni correnti dell'account del servizio SQL in una cartella passando alla scheda Sicurezza nelle proprietà della cartella corrispondente, selezionando il pulsante Avanzate e quindi usando la scheda Accesso effettivo.

Le operazioni di backup o ripristino che usano applicazioni di backup di terze parti hanno esito negativo

SQL Server offre uno strumento VDI (Virtual Backup Device Interface). Questa API consente ai fornitori di software indipendenti di integrare SQL Server nei propri prodotti per fornire supporto per le operazioni di backup e ripristino. Queste API sono progettati per garantire la massima affidabilità e prestazioni e per supportare l'intera gamma di funzionalità di backup e ripristino di SQL Server. Sono incluse le funzionalità complete di snapshot e backup ad accesso frequente.

Passaggi di risoluzione dei problemi comuni

• Per le versioni precedenti a SQL Server 2012, assicurarsi che il servizio SQLWriter sia avviato e che l'account di avvio sia impostato su Sistema locale. Assicurarsi inoltre che l'account di accesso NT AUTHORITY\SYSTEM esista in SQL Server e che faccia parte del ruolo del server Sysadmin dell'istanza in cui vengono eseguiti i backup.

• Per SQL Server 2012 e versioni successive, viene creato un nuovo account di accesso denominato [NT SERVICE\SQLWriter], di cui viene effettuato il provisioning come account di accesso durante l'installazione. Assicurarsi che questo account di accesso esista in SQL Server e faccia parte del ruolo del server Sysadmin.

• Assicurarsi che SqlServerWriter sia elencato quando il VSSADMIN LIST WRITERS comando viene eseguito al prompt dei comandi nel server che esegue SQL Server. Per consentire il completamento corretto dei backup del Servizio Copia Shadow del volume, il writer deve essere elencato come writer e il suo stato deve essere Stabile.

• Per altre informazioni, controllare i log del software di backup corrispondente e dei relativi siti di supporto.

  Sintomi o scenari	Articolo della Knowledge Base
  Backup di database con distinzione tra maiuscole e minuscole non riuscite	2987610 FIX: Errore durante il backup di un database con regole di confronto con distinzione tra maiuscole e minuscole tramite VSS in SQL Server 2012 SP2
  I backup di terze parti eseguiti tramite vss writer potrebbero non riuscire e restituire errori 8229.	2987610 FIX: Errore durante il backup di un database con regole di confronto con distinzione tra maiuscole e minuscole tramite VSS in SQL Server 2012 SP2
  Informazioni sul funzionamento del backup VDI	Funzionamento: SQL Server - Risorse di backup VDI (VSS)
  L'agente di Azure Site Recovery segnala un errore	L'agente ASR o un altro backup vss non componente ha esito negativo per un server che ospita SQL Server 2008 R2

Altre risorse

Funzionamento: numero di database di cui è possibile eseguire il backup contemporaneamente?

Problemi vari

Sintomo/scenario	Azioni correttive o informazioni aggiuntive
I backup potrebbero non riuscire se il rilevamento delle modifiche è abilitato nei database e restituisce errori simili ai seguenti: "Errore: 3999, gravità: 17, stato: 1. <TimeStamp> spid spid> <Failed to flush the commit table to disk in dbid 8 a causa dell'errore 2601. Per altre informazioni, vedere il log degli errori.	Vedere gli articoli della Microsoft Knowledge Base 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 2603910 CORREZIONE: il backup ha esito negativo in SQL Server 2008, in SQL Server 2008 R2 o in SQL Server 2012 se si abilita il rilevamento delle modifiche nel database 2522893 CORREZIONE: un'operazione di backup in un database SQL Server 2008 o SQL Server 2008 R2 ha esito negativo se si abilita il rilevamento delle modifiche in questo database
Problemi di ripristino dei backup dei database crittografati	Spostare un database protetto da TDE in un'altra istanza di SQL Server
Il tentativo di ripristinare un backup CRM dall'edizione Enterprise non riesce in un'edizione Standard	2567984 errore "Impossibile avviare il database in questa edizione di SQL Server" durante il ripristino di un database di Microsoft Dynamics CRM

Domande frequenti sulle operazioni di backup e ripristino di SQL Server

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

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

Cosa è necessario fare se SQL Server esegue il failover al centro del backup?

Riavviare l'operazione di ripristino o backup per riavvio di un'operazione di ripristino interrotta (Transact-SQL).

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

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

Ricerca per categorie verificare i backup del database di 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 di SQL Server è lo stesso negli ambienti a 64 bit e a 32 bit. Di conseguenza, le operazioni di backup e ripristino funzionano in ambienti a 64 bit e a 32 bit.

Suggerimenti generali sulla risoluzione dei problemi

• Assicurarsi di effettuare il provisioning delle autorizzazioni lettura e scrittura per l'account del servizio SQL Server nella cartella in cui vengono scritti i backup. Per altre informazioni, vedere Autorizzazioni per il backup.
• Assicurarsi che la cartella scritta dai backup disponga di spazio sufficiente per supportare i backup del database. È possibile usare la sp_spaceused stored procedure per ottenere una stima approssimativa delle dimensioni del backup per un database specifico.
• Usare sempre la versione più recente di SSMS per assicurarsi di non riscontrare problemi noti correlati alla configurazione dei processi e dei piani di manutenzione.
• Eseguire un'esecuzione di test dei processi per assicurarsi che i backup vengano creati correttamente. Aggiungere sempre 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 si notano errori di backup intermittenti, verificare se si verifica un problema già risolto nell'aggiornamento più recente per la versione di SQL Server. Per altre informazioni, vedere Versioni e aggiornamenti di SQL Server.
• Per pianificare e automatizzare i backup per le edizioni di SQL Express, vedere Pianificare e automatizzare i backup dei database di SQL Server in SQL Server Express.

Argomenti di riferimento per le operazioni di backup e ripristino di SQL Server

• Per altre informazioni sulle operazioni di backup e ripristino, vedere gli argomenti seguenti nella documentazione online di :

  "Backup e ripristino di database DI SQL Server": questo argomento illustra i concetti relativi alle operazioni di backup e ripristino per i database di SQL Server, fornisce collegamenti ad argomenti aggiuntivi e fornisce procedure dettagliate per eseguire vari backup o attività di ripristino, ad esempio la verifica dei backup e il backup tramite T-SQL o SSMS. Questo è l'argomento padre di questo argomento nella documentazione di SQL Server.

• Nella tabella seguente sono elencati altri argomenti che è possibile esaminare per attività specifiche correlate alle operazioni di backup e ripristino.

  Riferimento	Descrizione
  BACKUP (Transact-SQL)	Fornisce risposte alle domande di base relative ai backup. Fornisce esempi di diversi tipi di operazioni di backup e ripristino.
  Dispositivi di backup (SQL Server)	Fornisce un ottimo riferimento per comprendere i vari dispositivi di backup, il backup in una condivisione di rete, l'archiviazione BLOB di Azure e le attività correlate.
  Modelli di recupero (SQL Server)	Vengono illustrati in dettaglio i vari modelli di recupero: Simple, Full e Bulk-Logged. Fornisce informazioni sul modo in cui il modello di recupero influisce sui backup.
  Backup e ripristino: database di sistema (SQL Server)	Vengono illustrate le strategie e vengono illustrati gli aspetti da tenere presenti quando si lavora sulle operazioni di backup e ripristino dei database di sistema.
  Panoramica del ripristino e del recupero (SQL Server)	Illustra il modo in cui i modelli di ripristino influiscono sulle operazioni di ripristino. È consigliabile esaminare questa opzione se si hanno domande su come il modello di recupero di un database può influire sul processo di ripristino.
  Gestire i metadati quando si rende disponibile un database in un altro server	Quando un database viene spostato o si verificano problemi che influiscono su account di accesso, crittografia, replica, autorizzazioni e così via, è necessario tenere presenti diverse considerazioni.
  Uso dei backup del log delle transazioni	Vengono illustrati i concetti relativi a come eseguire il backup e il ripristino (applicare) i log delle transazioni nei modelli di recupero con registrazione completa e con registrazione minima delle operazioni bulk. Viene illustrato come eseguire backup di routine dei log delle transazioni (backup del log) per recuperare i dati.
  Backup gestito di SQL Server in Microsoft Azure	Introduce il backup gestito e le procedure associate.