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 -- Number of days to keep backup
SET @pathBackup = 'L:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' --Change path to the backup directory here.

-- Backup database
IF @BackupType = 1
   --SQL Backup 
   BEGIN
SET @CommandString = '-SQL "BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @pathBackup + '<DATABASE>\<AUTO>'' WITH DESCRIPTION = ''<AUTO>'', ERASEFILES_ATSTART = ' + convert(varchar(2),@nbDayToKeepBackup) + ', COMPRESSION = 3"'
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

-- Check if there is previous backups (order by date)
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 @nbDayToKeepBackup backup...
IF (SELECT count(1) FROM #B) > @nbDayToKeepBackup
BEGIN
-- Delete the first one
-- If you change @nbDayToKeepBackup to a lower number, you'll have to manually
-- remove old backup.
SET @DelString = 'del "' + @pathBackup + @DBName + '\' + (SELECT TOP 1 FName FROM #B) + '"'
EXEC master..xp_cmdshell @DelString, no_output
END
DROP TABLE #B

-- Set the path and the data backup file name
SET @pathFile = '''' + @pathBackup + @DBName + '\' + @DBName + '_backup_' + convert(varchar(8),getDate(),112) + '.bak'''
-- Create the backup
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
SET @CommandString = '-SQL "BACKUP LOG [' + @DBName + '] TO DISK = ''' + @pathBackup + '<DATABASE>\<AUTO>'' WITH DESCRIPTION = ''<AUTO>'', ERASEFILES_ATSTART = ' + convert(varchar(2),@nbDayToKeepBackup) + ', COMPRESSION = 3"'
EXEC master..sqlbackup @CommandString
END
ELSE

--Native Backup
BEGIN
-- Check previous logs backups
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 logs backups...
IF (SELECT count(1) FROM #L) > @nbDayToKeepBackup
BEGIN
-- Delete the first one
-- If you change @nbDayToKeepBackup to a lower number, you'll have to manually
-- remove old backup.
SET @DelString = 'del "' + @pathBackup + @DBName + '\' + (SELECT TOP 1 FName FROM #L) + '"'
EXEC master..xp_cmdshell @DelString, no_output
END
DROP TABLE #L

--Set the path and the logs backup file name
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