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