Automatizzare i backup database su SQL Express

Microsoft SQL Express manca della funzionalità Agent e quindi non è possibile creare dei piani per automatizzare i backup database.

Per eliminare l’inconveniente possiamo procedere utilizzando una procedura che suddivido in due passi:

1. Create una Store Procedure nel database master;
2. Creare un file .bat su cui poi attivare un task automatico

Relativamente al primo punto, procediamo con l’aprire la nostra SQL Server Management Studio, se non l’avete è bene installarla. La versione della SSMS deve essere almeno pari alla versione dei DB che avete installato.

Facciamo accesso al server, poi click su database, database di sistema, individuiamo il database chiamato master e facciamo click sul simbolo + per espandere la selezione. Tra le varie voci troviamo programmabilità, espandiamo anche questa facendo click su +.

Facciamo click con il tasto destro del mouse su Stored procedure e selezioniamo Nuova stored procedure.

Eliminiamo tutto il codice preimpostato e incolliamo il seguente codice:

USE [master] 
GO 
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
 
CREATE PROCEDURE [dbo].[sp_BackupDatabases] 
 @databaseName sysname = null,
 @backupType CHAR(1),
 @backupLocation nvarchar(200) 
AS 
 
 SET NOCOUNT ON; 
 
 DECLARE @DBs TABLE
 (
 ID int IDENTITY PRIMARY KEY,
 DBNAME nvarchar(500)
 )
 
 -- Pick out only databases which are online in case ALL databases are chosen to be backed up
 -- If specific database is chosen to be backed up only pick that out from @DBs
 INSERT INTO @DBs (DBNAME)
 SELECT Name FROM master.sys.databases
 where state=0
 AND name=@DatabaseName
 OR @DatabaseName IS NULL
 ORDER BY Name
 
 -- Filter out databases which do not need to backed up
 IF @backupType='F'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
 END
 ELSE IF @backupType='D'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE IF @backupType='L'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE
 BEGIN
 RETURN
 END
 
 -- Declare variables
 DECLARE @BackupName varchar(100)
 DECLARE @BackupFile varchar(100)
 DECLARE @DBNAME varchar(300)
 DECLARE @sqlCommand NVARCHAR(1000) 
 DECLARE @dateTime NVARCHAR(20)
 DECLARE @Loop int 
 
 -- Loop through the databases one by one
 SELECT @Loop = min(ID) FROM @DBs
 
 WHILE @Loop IS NOT NULL
 BEGIN
 
-- Database Names have to be in [dbname] format since some have - or _ in their name
 SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 
-- Set the current date and time n yyyyhhmmss format
 SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 
 
-- Create backup filename in path\filename.extension format for full,diff and log backups
 IF @backupType = 'F'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
 \ELSE IF @backupType = 'D'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
 ELSE IF @backupType = 'L'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
 
-- Provide the backup a name for storing in the media
 IF @backupType = 'F'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
 IF @backupType = 'D'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
 IF @backupType = 'L'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
 
-- Generate the dynamic SQL command to be executed
 
 IF @backupType = 'F' 
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
 END
 IF @backupType = 'D'
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END
 IF @backupType = 'L' 
 BEGIN
 SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END
 
-- Execute the generated SQL command
 EXEC(@sqlCommand)
 
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 
END

A questo punto facciamo click su Esegui nella SSMS console. Verrà creata quindi la Stored Procedure denominata sp_BackupDatabase

Adesso passiamo alla creazione degli script che ci permettono di effettuare i backup database. La stored procedure appena creata grazie ad appositi parametri permette di impostare la tipologia del backup database che può essere:

• Completo (F)
• Differenziale (D)
• Log (L)

Inoltre possiamo passare sempre come parametro il percorso da utilizzare per il salvataggio del file.

Procediamo quindi con il creare un file di testo che poi salveremo come .bat , all’interno di questo in base a quello che vogliamo impostare andiamo ad aggiungere il seguente codice:

sqlcmd -S .\SQLEXPRESS –E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’, @backupType=’F’”

questo comando ci permetterà di effettuare un backup database completo di tutti i database (F) nella cartella SQLBackups sotto il disco D.

Se volessimo invece effettuare un backup database differenziale, va utilizzato il seguente codice:

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q “EXEC sp_BackupDatabases  @backupLocation =’D:\SQLBackups\’, @BackupType=’D’”

In questo caso però non utilizzeremo l’autenticazione integrata, ma andremo ad utilizzare un account di SQL che abbia almeno il ruolo di Backup Operator. Andremo a sostituire SQLLogin con il nostro utente, password con la nostra password.

Per quanto riguarda i log invece il comando è il seguente:

Sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’,@backupType=’L’”

Nel caso invece volessimo effettuare il backup completo di un singolo database, possiamo utilizzare il seguente comando:

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’, @databaseName=’USERDB’, @backupType=’F’”

In questo caso andremo a sostituire USERDB con il database che ci interessa.

Ovviamente i file che andremo a creare dovranno essere utilizzati per pianificare delle attività attraverso lo strumento messo a disposizione dal sistema operativo.

Qualora vogliate utilizzare la modalità “D” quindi differenziale, ricordatevi di schedulare almeno un backup database database full. Ad esempio, potete schedulare un backup database Full la domenica ed ogni giorno un differenziale.