Pianificare e automatizzare i backup dei database SQL Server Express

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

Riepilogo

SQL Server Express non include SQL Server Agent, quindi non è possibile pianificare i processi di backup o i piani di manutenzione nel modo in cui è possibile usare altre edizioni SQL Server. Per automatizzare i backup in SQL Server Express, combina tre componenti: la stored procedure sp_BackupDatabases, l'utilità da riga di comando sqlcmd e Utilità di pianificazione di Windows. Questo articolo illustra i passaggi per configurare i backup completi, differenziali e del log delle transazioni pianificati dei database di SQL Server Express. Viene inoltre illustrato come confermare l'esecuzione di un backup pianificato.

Questo articolo si applica solo alle edizioni Express di SQL Server. Non si applica a SQL Server Express LocalDB.

Perché SQL Server Express non è in grado di pianificare i backup con SQL Server Agent

SQL Server Agent è il componente che esegue processi pianificati e piani di manutenzione e non è incluso in SQL Server Express. Per un confronto completo degli elementi inclusi in ogni edizione, vedere Edizioni e funzionalità supportate di SQL Server.

Senza SQL Server Agent, è comunque possibile eseguire il backup di un database SQL Server Express su richiesta usando uno degli strumenti seguenti:

Utilità sqlcmd
SQL Server Management Studio (SSMS)
estensione MSSQL per Visual Studio Code
Script di Transact-SQL che usa la famiglia di comandi BACKUP (Transact-SQL)

Per eseguire questi backup in base a una pianificazione anziché su richiesta, usare Windows Utilità di pianificazione come motore di pianificazione, come descritto nei passaggi seguenti. Per informazioni generali sui tipi di backup e sulla strategia, vedere Backup e ripristino di database SQL Server.

Prerequisites

Prima di iniziare, assicurarsi di avere:

Istanza in esecuzione di SQL Server Express. Gli esempi in questo articolo usano l'istanza denominata predefinita , .\SQLEXPRESS.
Appartenenza al ruolo predefinito del server sysadmin in tale istanza, necessaria per creare una stored procedure nel master database.
Una cartella o un'unità locale con spazio libero sufficiente per contenere i file di backup. Negli esempi viene usato D:\SQLBackups.
Autorizzazione per creare attività pianificate nel computer che esegue SQL Server Express.

Passaggio 1: Creare la stored procedure sp_BackupDatabases

sp_BackupDatabases è una stored procedure fornita da Microsoft che esegue il comando BACKUP appropriato su un database o su tutti i database online dell'istanza. Lo si crea una sola volta nel database master.

Scaricare lo script da SQL_Express_Backups.sql e salvarlo in locale, ad esempio C:\Temp\SQL_Express_Backups.sql. Quindi, creare la stored procedure utilizzando SSMS oppure sqlcmd.

Creare la stored procedure usando SSMS

Aprire SSMS e connettersi all'istanza di SQL Server Express. In Nome server immettere .\SQLEXPRESSe quindi selezionare Connetti.
Selezionare File>apri>file, selezionare il file SQL_Express_Backups.sql salvato e quindi selezionare Apri.
Verificare che l'elenco di database sulla barra degli strumenti mostri master. Lo script inizia con USE [master], quindi è destinato automaticamente al database corretto.
Selezionare Esegui (o premere F5). Il riquadro Messaggi segnala Commands completed successfully.

Creare la stored procedure usando sqlcmd

Al prompt dei comandi eseguire il comando seguente:

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Verificare che la stored procedure sia stata creata

Eseguire la query seguente sull'istanza. Restituisce una riga se la stored procedure esiste:

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

Parametri di sp_BackupDatabases

La stored procedure accetta i parametri seguenti:

Parametro	Obbligatorio	Description
`@backupLocation`	Yes	La cartella che riceve i file di backup, ad esempio `D:\SQLBackups\`. La cartella deve già esistere.
`@backupType`	Yes	Tipo di backup: `F` per completo, `D` differenziale o `L` per il log delle transazioni.
`@databaseName`	No	Il database di cui eseguire il backup. Se si omette questo parametro, la stored procedure esegue il backup di ogni database online nell'istanza ad eccezione dei database esclusi dallo script.

La procedura archiviata non effettua mai il backup di tempdb. Ignora anche i database di esempio Northwind, pubs e AdventureWorks per ogni tipo di backup e ignora master per i backup differenziali e del log perché questi tipi di backup non sono supportati per il database master.

Importante

I nomi dei database esclusi sono codificati direttamente nello script. Se si dispone di un database che usa uno di questi nomi, la stored procedure lo ignora senza segnalare un errore. Per eseguire il backup di tale database, modificare gli elenchi DELETE @DBs WHERE DBNAME IN (...) nello script prima di creare la procedura archiviata.

Passaggio 2: Installare l'utilità sqlcmd

L'utilità sqlcmd consente di eseguire istruzioni Transact-SQL, stored procedure di sistema e file di script dalla riga di comando. Viene installato con SSMS ed è disponibile anche come download autonomo per i computer che non hanno SSMS. Per installarlo, vedere Scaricare e installare l'utilità sqlcmd.

Per verificare che sqlcmd sia installato e disponibile, eseguire sqlcmd -? da un prompt dei comandi.

La cartella che contiene l'eseguibile sqlcmd viene in genere aggiunta alla Path variabile di ambiente quando si installa SQL Server o gli strumenti autonomi. Se sqlcmd -? segnala che il comando non viene riconosciuto, aggiungere la cartella alla Path variabile o specificare il percorso completo dell'utilità nel file batch.

Passaggio 3: Creare il file batch Sqlbackup.bat

In un editor di testo creare un file batch denominato Sqlbackup.bat. Copiare il testo da uno degli esempi seguenti in tale file, a seconda dello scenario in uso.

Prima di scegliere un esempio, considerare i punti seguenti:

Ogni esempio usa D:\SQLBackups come segnaposto. Modificare il percorso dell'unità e della cartella di backup da usare nell'ambiente e assicurarsi che la cartella esista.
Se si usa SQL Server l'autenticazione, la password viene archiviata in testo non crittografato nel file batch. Limitare l'accesso alla cartella che contiene il file batch solo agli utenti autorizzati.

Esempio 1: Backup completi di tutti i database tramite Autenticazione di Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Esempio 2: backup differenziali di tutti i database usando l'autenticazione SQL Server

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

Note

Per eseguire i backup, l'account di accesso deve essere membro del ruolo predefinito del database db_backupoperator o db_owner in ogni database di cui si esegue il backup o un membro del ruolo predefinito del server sysadmin . Per altre informazioni, vedere Ruoli a livello di database.

Esempio 3: Backup del log delle transazioni di tutti i database usando l'autenticazione Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='L'"

I backup del log delle transazioni richiedono il modello di ripristino con registrazione completa o con registrazione minima e richiedono almeno un backup completo precedente. Per altre informazioni, vedere Modelli di ripristino (SQL Server).

Esempio 4: backup completo di un singolo database usando Windows'autenticazione

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

Per eseguire un backup differenziale di USERDB, modificare il @backupType parametro in D. Per eseguire un backup del log delle transazioni, modificarlo in L.

L'opzione -b fa terminare sqlcmd e restituisce un valore DOS ERRORLEVEL di 1 quando un errore di SQL Server ha un livello di gravità maggiore di 10. Senza -b, sqlcmd restituisce 0 anche quando il backup ha esito negativo e Utilità di pianificazione segnala l'attività come riuscita.

Prima di continuare, eseguire Sqlbackup.bat manualmente da un prompt dei comandi e verificare che i file di backup vengano visualizzati nella cartella di backup. La correzione degli errori è ora più semplice rispetto alla diagnosi in un secondo momento tramite l'Utilità di pianificazione.

Passaggio 4: Pianificare il file batch nell'Utilità di pianificazione di Windows

Seguire questa procedura per eseguire Sqlbackup.bat in base a una pianificazione:

Nel computer che esegue SQL Server Express selezionare Avvia e digitare Utilità di pianificazione nella casella di testo.
In Corrispondenza migliore, selezionare Utilità di pianificazione per avviarla.
In Utilità di pianificazione fare clic con il pulsante destro del mouse su Utilità di pianificazione (locale) e selezionare Crea attività di base.
Immettere un nome per la nuova attività , ad esempio SQLBackup, e quindi selezionare Avanti.
Selezionare Giornaliero come attivatore dell'attività, quindi selezionare Avanti.
Impostare la ricorrenza su un giorno e quindi selezionare Avanti.
Selezionare Avvia un programma come azione e quindi selezionare Avanti.
Selezionare Sfoglia, selezionare il fileSqlbackup.bat creato nel passaggio 3: Creare il file batch Sqlbackup.bat e quindi selezionare Apri.
Selezionare la casella di controllo Apri la finestra di dialogo Proprietà per questa attività quando si fa clic su Fine e quindi selezionare Fine.
Nella scheda Generale esaminare le opzioni di sicurezza e verificare quanto segue per l'account elencato in Quando si esegue l'attività, usare l'account utente seguente:
- L'account dispone almeno delle autorizzazioni Lettura ed Esecuzione per eseguire l'utilità sqlcmd .
- Se il file batch usa Windows Authentication, l'account dispone dell'autorizzazione per eseguire il backup dei database SQL Server.
- Se il file batch usa SQL Server Authentication, l'account di accesso SQL Server nel file batch dispone dell'autorizzazione per eseguire il backup dei database.
Modificare le impostazioni rimanenti in base ai requisiti e quindi selezionare OK.

Suggerimento

Per prova, esegua Sqlbackup.bat da un prompt dei comandi che ha avviato con lo stesso account utente proprietario dell'attività. Questo passaggio conferma che l'account dispone delle autorizzazioni necessarie prima dell'esecuzione della pianificazione.

Per ulteriori informazioni sulle opzioni di pianificazione, vedere Utilità di pianificazione.

Verificare che il backup pianificato sia stato eseguito

Dopo la prima esecuzione pianificata, verificare che il backup sia riuscito:

Controllare la cartella di backup per i nuovi file di .bak (backup completi e differenziali) o i file con estensione trn (backup del log delle transazioni). La stored procedure aggiunge un indicatore di data e ora a ogni nome di file.
In Utilità di pianificazione, seleziona Libreria Utilità di pianificazione, seleziona l'attività e controlla la colonna Ultimo risultato dell'esecuzione. Questa colonna mostra il codice di uscita restituito dal file batch. Poiché gli esempi usano l'opzione -b , il valore 0x1 indica che il comando di backup non è riuscito. Per altri valori, vedi costanti di errore e di esito positivo dell'Utilità di pianificazione.
Eseguire una query sulla cronologia di backup nell'istanza di :
```
SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;
```
Queste tabelle fanno parte della cronologia di backup che SQL Server gestisce nel msdb database. Per altre informazioni, vedere Informazioni sulla cronologia di backup e sull'intestazione (SQL Server).For more information, see Backup history and header information (SQL Server).