Backup database (Red Gate/Native Backup)

------------------------------------------------------------------------------------------------------
-- Authors : Duquene Jerome / Bereznuk Eugene
--
-- Description : backup a database in a specific directory with subfolder database named.
--               By default, perform an Red Gate SQL Backup with 0 as second parameter, perform
--               native SQL backup.
--
-- Parameter :
-- Input : - the name of the database which needs to be backuped.
--         - 1 or 0 - 1 (default value) to perform a SQL Backup, 0 to perform a native backup
-- Output : Create a backup file.
--
-- "Setup variables" - need to be set for each server before performing the creation of the procedure:
-- @nbDayToKeepBackup : how many days you want to keep backups.
-- @pathBackup : Path to the server backups directory.
------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE Tech_BackupDatabase
@DBName varchar(100) = '',
@BackupType bit = 1     -- Default use SQL Backup, set 0 use native backup
AS

SET NOCOUNT ON
IF LEN(@DBName) > 0
BEGIN
DECLARE @exitcode int
DECLARE @sqlerrorcode int
DECLARE @CommandString nvarchar(1000)
DECLARE @pathBackup varchar(500) -- Server path to the backup directory
DECLARE @pathFile varchar(1000)  -- Use for complete path of backup file & log file
DECLARE @DelString varchar(500)
DECLARE @cmdDir varchar(500)
DECLARE @nbDayToKeepBackup int


SET @nbDayToKeepBackup = 2
SET @pathBackup = 'L:\MSSQL\Mssql\Backup\' --Change path to the backup directory here.

-- Backup database
IF @BackupType = 1
   --SQL Backup 
   BEGIN
IF @nbDayToKeepBackup = 0
BEGIN
SET @CommandString = '-SQL "BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @pathBackup + '<DATABASE>\<AUTO>'' WITH DESCRIPTION = ''<AUTO>'', ERASEFILES_ATSTART = 1h , COMPRESSION = 3"'
END
ELSE
BEGIN
SET @CommandString = '-SQL "BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @pathBackup + '<DATABASE>\<AUTO>'' WITH DESCRIPTION = ''<AUTO>'', ERASEFILES_ATSTART = ' + convert(varchar(2),@nbDayToKeepBackup) + ', COMPRESSION = 3"'
END
EXEC master..sqlbackup @CommandString
   END
ELSE
   --Native Backup  
   BEGIN
-- Check if a folder database named exist or not
CREATE TABLE #FOLDER (FName varchar(100))
SET @cmdDir = 'dir "' + @pathBackup + '" /b /O > C:\tempfolder.txt'
EXEC master..xp_cmdshell @cmdDir, no_output
BULK INSERT #FOLDER FROM 'C:\tempfolder.txt' WITH (DATAFILETYPE = 'char', ROWTERMINATOR = '\n')
EXEC master..xp_cmdshell 'del C:\tempfolder.txt', no_output
IF(SELECT count(1) FROM #FOLDER WHERE FName = @DBName) < 1
BEGIN
--If don't exist, create a directory with the name of the database
SET @cmdDir = 'mkdir "' + @pathBackup + @DBName + '"'
EXEC master..xp_cmdshell @cmdDir, no_output
END
DROP TABLE #FOLDER

CREATE TABLE #B (FName varchar(100))
SET @cmdDir = 'dir "' + @pathBackup + @DBName + '\*.bak" /b /O > C:\tempdir.txt'
EXEC master..xp_cmdshell @cmdDir, no_output
BULK INSERT #B FROM 'C:\tempdir.txt' WITH (DATAFILETYPE = 'char', ROWTERMINATOR = '\n')
EXEC master..xp_cmdshell 'del C:\tempdir.txt', no_output
-- Check if there is more than 0 backup...
IF (SELECT count(1) FROM #B) > @nbDayToKeepBackup
BEGIN
SET @DelString = 'del "' + @pathBackup + @DBName + '\' + (SELECT TOP 1 FName FROM #B) + '"'
EXEC master..xp_cmdshell @DelString, no_output
END
DROP TABLE #B
SET @pathFile = '''' + @pathBackup + @DBName + '\' + @DBName + '_backup_' + convert(varchar(8),getDate(),112) + '.bak'''
EXEC ('BACKUP DATABASE [' + @DBName + '] TO DISK = ' + @pathFile + ' WITH INIT, SKIP')
   END
--Error check
IF (@exitcode <> 0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup database step failed: exit code %d, SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
-- Backup logs (only if the database recovery <> Simple)
IF (SELECT RTRIM(CAST(DATABASEPROPERTYEX(@DBName, 'Recovery') AS varchar(50)))) <> 'SIMPLE'
    BEGIN
    IF @BackupType = 1
--SQL Backup
BEGIN
IF @nbDayToKeepBackup = 0
BEGIN
SET @CommandString = '-SQL "BACKUP LOG [' + @DBName + '] TO DISK = ''' + @pathBackup + '<DATABASE>\<AUTO>'' WITH DESCRIPTION = ''<AUTO>'', ERASEFILES_ATSTART = 1h , COMPRESSION = 3"'
END
ELSE
BEGIN
SET @CommandString = '-SQL "BACKUP LOG [' + @DBName + '] TO DISK = ''' + @pathBackup + '<DATABASE>\<AUTO>'' WITH DESCRIPTION = ''<AUTO>'', ERASEFILES_ATSTART = ' + convert(varchar(2),@nbDayToKeepBackup) + ', COMPRESSION = 3"'
END
EXEC master..sqlbackup @CommandString
END
ELSE
--Native Backup
BEGIN
CREATE TABLE #L (FName varchar(100))
SET @cmdDir = 'dir "' + @pathBackup + @DBName + '\*.log" /b /O > C:\tempdir.txt'
EXEC master..xp_cmdshell @cmdDir, no_output
BULK INSERT #L FROM 'C:\tempdir.txt' WITH (DATAFILETYPE = 'char', ROWTERMINATOR = '\n')
EXEC master..xp_cmdshell 'del C:\tempdir.txt', no_output
-- Check if there is more than @nbDayToKeepBackup backup...
IF (SELECT count(1) FROM #L) > @nbDayToKeepBackup
BEGIN
SET @DelString = 'del "' + @pathBackup + @DBName + '\' + (SELECT TOP 1 FName FROM #L) + '"'
EXEC master..xp_cmdshell @DelString, no_output
END
DROP TABLE #L
SET @pathFile = '''' + @pathBackup + @DBName + '\' + @DBName + '_log_' + convert(varchar(8),getDate(),112) + '.log'''
EXEC ('BACKUP LOG [' + @DBName + '] TO DISK = ' + @pathFile + ' WITH INIT, SKIP')
END
--Error check
IF (@exitcode <> 0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup Log step failed: exit code %d, SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
END

END
GO