Risolvere i problemi relativi alle operazioni di backup e ripristino SQL Server

Questo articolo fornisce soluzioni per i problemi comuni che potrebbero verificarsi durante le operazioni di backup e ripristino di Microsoft SQL Server e fornisce riferimenti ad altre 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 elevato utilizzo di I/O. La velocità effettiva di backup/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 siano state arrestate o il completamento richiede troppo tempo, è possibile usare uno o più dei metodi seguenti per stimare il tempo per il completamento o per tenere traccia dello stato di avanzamento di un'operazione di backup o ripristino:

• Il log degli errori 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 ripristinare il 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 delle operazioni di backup e ripristino.

• È possibile usare la percent_complete colonna di sys.dm_exec_requests per tenere traccia dello stato 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 prestazioni e Backup/Restore throughput/sec . Per altre informazioni, vedere SQL Server Oggetto dispositivo di backup.

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

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

Elementi da controllare

1. Controllare se si verificano i 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 Documentazione online illustra varie procedure consigliate che è possibile usare per migliorare le prestazioni delle operazioni di backup/ripristino. Ad esempio, è possibile assegnare il SE_MANAGE_VOLUME_NAME privilegio speciale all'account di Windows che esegue SQL Server per abilitare l'inizializzazione immediata dei file di dati. Ciò può produrre miglioramenti significativi delle prestazioni.
   2920151 Hotfix e aggiornamenti consigliati per i cluster di failover basati su Windows Server 2012 R2 aggiornamento cumulativo 2822241 Windows 8 e Windows Server 2012: aprile 2013	I rollup del sistema correnti possono includere correzioni per problemi noti a livello di sistema che possono compromettere le prestazioni di programmi come SQL Server. L'installazione di questi aggiornamenti consente di evitare tali problemi.
   2878182 FIX: i processi in modalità utente in un'applicazione non rispondono ai 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 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	Viene descritto 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 crescere oltre una determinata dimensione	Viene illustrato un problema che si verifica quando un file system NTFS è fortemente frammentato.
   304101 programma di backup non riesce quando si esegue il backup di un volume di sistema di grandi dimensioni
   2455009 FIX: prestazioni lente quando si recupera un database se sono presenti molti VLF all'interno del 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ò è particolarmente vero durante la fase di ripristino dell'operazione di ripristino. Per informazioni su altri possibili problemi che possono essere causati dalla presenza di molti VLF, vedere Il completamento delle operazioni del database richiede molto tempo oppure genera errori quando il log delle transazioni contiene numerosi file di log virtuali.
   Un'operazione di backup o ripristino in un percorso di rete è lenta	Isolare il problema in 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 SQL Server e nel registro eventi di Windows per altri puntatori sulla 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 la contesa sull'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 di SQL Server diverse

Non è possibile ripristinare un backup 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 su 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: backup del database in un server che esegue la versione %ls. Tale versione non è compatibile con questo server, che esegue la versione %ls. Ripristinare il database in un server che supporta il backup oppure usare 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.

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 SQL Server versione. Selezionare anche l'opzione più adatta per salvare gli script generati. Continuare quindi la procedura guidata.
   3. Usare l'utilità del 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 ognuna delle tabelle disabilitare eventuali vincoli e trigger di chiave esterna. Se la tabella include colonne Identity, abilitare l'inserimento di identità.
   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 della chiave esterna e disabilitare l'inserimento di identità per ognuna delle tabelle interessate nel passaggio c.

Questa procedura funziona in genere bene 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), 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 script per un database usando l'opzione Genera script.

Problemi del processo di backup negli ambienti Always On

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

• Per impostazione predefinita, la preferenza di backup automatico è impostata su Prefer Secondary.By default, the automatic backup preference is set to Prefer Secondary. In questo modo si 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à Always On
• Offload dei 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 file di backup danneggiato. Di seguito sono riportati alcuni esempi di errori che è possibile ottenere se un set di backup è danneggiato:

• 3241: il formato della famiglia di supporti nel dispositivo '%ls' non è corretto. SQL Server non è possibile elaborare questa famiglia di supporti.

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

• 3243: la famiglia di supporti nel dispositivo '%ls' è stata creata usando la versione del formato nastro Microsoft %d.%d. SQL Server supporta la versione %d.%d.

Questi problemi possono verificarsi a causa di problemi che influiscono sull'hardware sottostante (dischi rigidi, archiviazione di rete e così via) o correlati a un virus o malware. Esaminare i log eventi e i log hardware del sistema Windows per individuare gli errori segnalati ed eseguire le azioni appropriate, ad esempio aggiornare il firmware o risolvere i problemi di rete.

Per evitare questi errori, abilitare l'opzione BACKUP CHECKSUM quando si esegue un backup per evitare il backup di un database danneggiato. Per altre informazioni, vedere Possibili errori multimediali 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.

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:

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

• Scenario 2: i backup pianificati hanno esito negativo e generano un messaggio di errore che viene registrato nella cronologia dei processi 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 di 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 sempre 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.

Per altre informazioni, vedere Dispositivi di backup.

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

SQL Server fornisce 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 progettate per offrire la massima affidabilità e prestazioni e per supportare l'intera gamma di funzionalità di backup e ripristino SQL Server. Include l'intera gamma di funzionalità di snapshot e backup a caldo.

Procedura per la 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] ed è stato eseguito 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. Questo writer deve essere elencato come writer e deve essere nello stato Stabile per consentire il completamento dei backup di VSS.

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

  Sintomi o scenario	Articolo della Microsoft Knowledge Base
  Backup dei database con distinzione tra maiuscole e minuscole non riusciti	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 il writer vss 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	Errore dell'agente ASR o di un altro backup vss non componente per un server che ospita SQL Server 2008 R2

Altre risorse

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

Problemi vari

Domande frequenti sulle operazioni di backup e ripristino 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 ha esito negativo durante il backup?

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

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

SQL Server backup non può essere ripristinato 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 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 backup a 32 bit in server a 64 bit e viceversa?

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

Suggerimenti generali per la risoluzione dei problemi

• Assicurarsi di effettuare il provisioning delle autorizzazioni di 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 in cui vengono scritti i backup abbia spazio sufficiente per supportare i backup del database. È possibile utilizzare 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 relativi 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 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 SQL Server. Per altre informazioni, vedere SQL Server Versioni e aggiornamenti.
• Per pianificare e automatizzare i backup per le edizioni di SQL Express, vedere Pianificare e automatizzare i backup dei database SQL Server in SQL Server Express.

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

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

  "Backup e ripristino di database SQL Server": questo argomento illustra i concetti delle operazioni di backup e ripristino per i database SQL Server, fornisce collegamenti ad argomenti aggiuntivi e fornisce procedure dettagliate per eseguire diversi backup o attività di ripristino, ad esempio la verifica dei backup e il backup tramite T-SQL o SSMS. Questo è l'argomento padre su 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 correlate 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 di una condivisione di rete, l'archiviazione BLOB di Azure e le attività correlate.
  Modelli di ripristino (SQL Server)	Illustra in dettaglio i vari modelli di ripristino: Simple, Full e Bulk-Logged. Fornisce informazioni sul modo in cui il modello di ripristino influisce sui backup.
  Backup & ripristino: database di sistema (SQL Server)	Illustra le strategie e illustra gli elementi da tenere presenti quando si lavora alle operazioni di backup e ripristino dei database di sistema.
  Panoramica di ripristino e ripristino (SQL Server)	Illustra in che modo i modelli di ripristino influiscono sulle operazioni di ripristino. È consigliabile esaminarlo se si hanno domande sul modo in cui 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	Varie considerazioni da tenere presenti quando un database viene spostato o si verificano problemi che influiscono su account di accesso, crittografia, replica, autorizzazioni e così via.
  Uso dei backup del log delle transazioni	Vengono illustrati i concetti su come eseguire il backup e il ripristino dei log delle transazioni (applicare) nei modelli di ripristino con registrazione completa e bulk. Viene illustrato come eseguire backup di routine dei log delle transazioni (backup del log) per ripristinare i dati.
  SQL Server Backup gestito in Microsoft Azure	Introduce il backup gestito e le procedure associate.