Generic SQL backup with Azure support

--############################################################################################################################
--
-- This script is being offered for public use and as such is being offered as untested and unverified.
-- Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments 
-- that are NOT under my control.
-- 
-- Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours, 
-- or your company needs!
--
--############################################################################################################################

USE [<your admin database name in here>]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [<your schema name in here>].[generic_backup_all_databases_cloud]

--#############################################################
--
-- Author: Haden Kingsland
-- Date: 18/08/2008
-- Version: 01:00
--
-- Desc: To backup all databases given certain parameters
--
--#############################################################################
-- YOU MUST BE A SYSADMIN IN ORDER TO SWITCH ON XP_CMDSHELL, SO YOU MUST ENSURE THAT THE 
-- AGENT JOB FOR ALL BACKUPS RUNS UNDER THE CONTEXT OF A SYSADMIN USER!
--#############################################################################
--
-- You will need to create a credential for your Azure account in order to perform
-- backups to this location. This must relate to the Blob storage location in azure...
--
-- http://msdn.microsoft.com/en-GB/library/jj919148(v=sql.110).aspx#Support
-- http://azure.microsoft.com/en-us/documentation/articles/storage-manage-storage-account/
-- http://msdn.microsoft.com/en-us/library/jj919149.aspx

------------------------------------------------------------
-- EXAMPLE CALL TO SP VIA a SQL SERVER AGENT JOB STEP
------------------------------------------------------------
--Differential is type "D" and Daily
--Full is type "F" and Weekly
--Transaction Log is type "T" and Daily or Weekly
--IsCloud -- it this going to be a Cloud (URL) backup or not?
--Adhoc is any type plus a database name must be present
--For a copy backup @copy must be a value of "1"
--System is type "S" and Daily or Weekly
--create a directory where ones doesn't exist
--create a production and non-production backup
--process SIMPLE mode db's or not
--either ignore specified database passed in if set to 'Y' or only process those passed in if set to 'N'
--@checksum tells the backup process whether to perform a checksum once the backup is complete so that we can verify its integrity.
--the value of @checksum can be either 1 = Yes or 0 = No!

--declare@RC int
--declare@job_name varchar(128)
--declare@BACKUP_LOCATION varchar(200)
--declare@isCloud varchar(1)
--declare@backup_type VARCHAR(1)
--declare@dbname nvarchar(2000)
--declare@copy varchar(1)
--declare@freq varchar(10)
--declare  @production varchar(1)
--declare @INCSIMPLEvarchar(1) 
--declare @ignoredb varchar(1)
--declare @checksum varchar(1)
--declare @isSP varchar(1)
--declare@recipient_list varchar(2000)

 
-- select @job_name = '<your server/instance name> Database Backups'
-- select @backup_location = 'https://<your Azure Subscription>.blob.core.windows.net/sqlbak/'
-- select@isCloud = 'Y'
-- select @backup_type = 'F' -- 'F', 'S', 'D', 'T'
-- select @dbname ='' --  a valid database name or spaces
-- select @copy = 0 -- 1 or 0 -- copy only backup or not
-- select @checksum = 1 -- 1 or 0 -- create a checksum for backup integrity validation
-- select @freq = 'Daily' -- 'Weekly', 'Daily'
-- select @production = 'Y' -- 'Y', 'N' -- only use 'N' for non production instances
-- select @INCSIMPLE = 'Y' -- 'Y', 'N' -- include SIMPLE recovery model databases
-- select @ignoredb = 'N' -- 'Y' or 'N' -- if "Y" then it will ignore the databases in the @dbname parameter
-- select@isSP = 'N' -- 'Y' or 'N' -- set to Y if the instance is used for SharePoint. Implemented due to extra long SP database names!
-- select@recipient_list = 'hkingsland@laingorourke.com' -- ;rfinney@laingorourke.com'

-- EXECUTE @RC = [dbadmin].[dba].[generic_backup_all_databases_cloud] 
-- @backup_location,
-- @isCloud,
-- @job_name,
-- @backup_type,
-- @dbname,
-- @copy,
-- @freq,
-- @production,
-- @INCSIMPLE,
-- @ignoredb,
-- @checksum,
-- @isSP,
-- @recipient_list
--
-----------------------
-- Modification History
-----------------------
--
-- 28/10/2008 -- Haden Kingsland --Added link to Idera SQLDM to email body for Ops.
-- 16/01/2009 -- Haden Kingsland --Amended procedure to be generic for all backup types.
-- 26/01/2009 -- Haden Kingsland --Amended procedure to create directory structure if run for a production backup
--and to enhance error handling and reported error messages.
-- 26/02/2009 -- Haden Kingsland --To add the @INCSIMPLE flag to allow for differential backups of SIMPLE mode databases 
-- 09/03/2009 -- Haden Kingsland --To cater for a status of "-2" being returned from the cursor
-- 18/03/2009 -- Haden Kingsland --To cater for new databases that do not yet have a WEEKLY FULL database backup, when
--the request is for a TX LOG or DIFF backup. Backup type changed to FULL under these
--circumstances
--  31/03/2009 -- Haden Kingsland --Changed to include BULK-LOGGED mode databases for FULL backups.
--Added the option to check whether xp_cmdshell is on, and if not, turn it on for 
--the duration on this procedure.
--  07/04/2009 -- Haden Kingsland --Added a check for "m.mirroring_role <> 1" to ignore all databases acting as a mirror
--
--  01/09/2010 -- Haden Kingsland --Changed the select statement so that it checks for multiple passed in database names and either 
--processes all that are passed in, or ignores those that are passed in via a new paramater 
--call @ignoredb
-- 
--22/11/2012 -- Haden Kingsland --To add version checking enhancements for SQL 2012 so that backup compression occurs natively
--for appropriate versions
--
--03/02/2014 -- Haden Kingsland --Added the option to allow for a checksum when performing a backup.
--
--11/03/2014 -- Haden Kingsland --Added the @isSP parameter to cater for extraordinary long SharePoint database names!
--Also added ltrim(rtrim() to the @dbname_dir parameter to remove leading and trailing spaces.
--Added in a link to the SCOM console in the backup failure email, for when an LOR backup fails
--
--  14/03/2014  -- Haden KingslandNow changed to email a list of recipients passed in at runtime upon failure of any one
--database backup of any type, or if blank, to check for the failsafe operator and email 
--these addresses.
--
--  11/12/2014  -- Haden KingslandSupport added for backing up to a URL (Azure) outside of the maintenance plan option (SQL 2014 -->)
--
--#################################################################
 (
    @BACKUP_LOCATIONvarchar(200),
    @isCloudvarchar(1), -- ('Y' or 'N')
@job_nameVARCHAR(128),
@backup_typeVARCHAR(1), 
@dbnamenvarchar(2000),
@copyvarchar(1),
@freqvarchar(10),
@productionvarchar(1), -- ('Y' or 'N')
@INCSIMPLEvarchar(1), -- ('Y' or 'N')
@ignoredbvarchar(1), -- ('Y' or 'N')
@checksumvarchar(1),-- ('Y' or 'N')
@isSPvarchar(1),-- ('Y' or 'N') -- Used to identify whether an instance is used for SharePoint!
@recipient_listvarchar(2000) -- list of people to email on failure separated by semi-colon.
  )
  
as

BEGIN

DECLARE@database_name nvarchar(300),
@dbname_dir varchar(300),
@DATEH VARCHAR(12),
@BACKUP_TRAN VARCHAR(500),
@BACKUP_DB VARCHAR(500),
@MailProfileName VARCHAR(50),
@COMMAND varchar(800),
@ERR_MESSAGE varchar(200),
@ERR_NUM int,
@MESSAGE_BODY varchar(600),
@MESSAGE_BODY_OPS varchar(600),
@MESSAGE_BODY2 varchar(600),
@MESSAGE_BODY3 varchar(600),
@mirrorrole tinyint,
@mirror_status tinyint,
@role varchar(15),
@ver varchar(15),
@sql varchar(1000),
@ext varchar(6),
@MACHINE varchar(20),
@Q CHAR(1),
@dir_type varchar(10),
@dir varchar(400),
@subdir varchar(250),
@diff varchar(12),
@full_backup_name varchar(30),
@orig_backup_type varchar(1),
@changed_backup_type varchar(1),
@freq_changed varchar(1),
@orig_freq varchar(10),
@level varchar(2),
@XPCMDSH_ORIG_ON varchar(1),
@credential varchar(30),
@failsafe VARCHAR(100);


-- ##########
-- START DEBUG for Cloud backup
-- ##########

--declare@RC int
--declare @job_name varchar(128)
--declare@BACKUP_LOCATION varchar(200)
--declare@isCloud varchar(1)
--declare@backup_type VARCHAR(1)
--declare@dbname nvarchar(2000)
--declare@copy varchar(1)
--declare@freq varchar(10)
--declare  @production varchar(1)
--declare @INCSIMPLEvarchar(1) 
--declare @ignoredb varchar(1)
--declare @checksum varchar(1)
--declare @isSP varchar(1)
--declare@recipient_list varchar(2000)

-- select @job_name = '<your database server/instance> Database Backups'
-- select @backup_location = 'https://<your Azure Subscription>.blob.core.windows.net/<your Azure container>/' -- physical location or Azure URL
-- select@isCloud = 'Y'
-- select @backup_type = 'F' -- 'F', 'S', 'D', 'T'
-- select @dbname ='' --  a valid database name or spaces
-- select @copy = 0 -- 1 or 0 -- copy only backup or not
-- select @checksum = 1 -- 1 or 0 -- create a checksum for backup integrity validation
-- select @freq = 'Daily' -- 'Weekly', 'Daily'
-- select @production = 'Y' -- 'Y', 'N' -- only use 'N' for non production instances
-- select @INCSIMPLE = 'Y' -- 'Y', 'N' -- include SIMPLE recovery model databases
-- select @ignoredb = 'Y' -- 'Y' or 'N' -- if "Y" then it will ignore the databases in the @dbname parameter
-- select@isSP = 'N' -- 'Y' or 'N' -- set to Y if the instance is used for SharePoint. Implemented due to extra long SP database names!
-- select@recipient_list = '<your email>.com' -- ;<another email>.com'


-- #########
-- END DEBUG
-- #########

-- initialize the variables

select @credential = name 
from sys.credentials
where credential_identity like '%<your required Azure credential>%'

select * from sys.credentials

set @orig_backup_type = ''
set @orig_freq = ''
set @changed_backup_type = 'N'
set @freq_changed = 'N' 
set @XPCMDSH_ORIG_ON = ''

SELECT @ver = CASE WHEN @@VERSION LIKE '%9.0%'THEN 'SQL 2005' 
   WHEN @@VERSION LIKE '%8.0.%'THEN 'SQL 2000'
   WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008' 
   WHEN @@VERSION LIKE '%10.5%' THEN 'SQL 2008 R2' 
   WHEN @@VERSION LIKE '%11.0%' THEN 'SQL 2012'
   WHEN @@VERSION LIKE '%12.0%' THEN 'SQL 2014' 
END;

select @@VERSION

SELECT @level =CASE 
WHEN convert(varchar(30),serverproperty('Edition'))  LIKE '%Enterprise%' THEN 'EE' 
WHEN convert(varchar(30),serverproperty('Edition'))  LIKE '%Developer%' THEN 'DE' 
WHEN convert(varchar(30),serverproperty('Edition'))  LIKE '%Standard%' THEN 'SE'
WHEN convert(varchar(30),serverproperty('Edition'))  LIKE '%Web%' THEN 'WE' 
WHEN convert(varchar(30),serverproperty('Edition'))  LIKE '%Express%' THEN 'EX'
WHEN convert(varchar(30),serverproperty('Edition'))  LIKE '%Business%' THEN 'BI' 
ELSE 'UNKNOWN'
END;

SELECT @full_backup_name = CASE WHEN @backup_type = 'F' THEN 'Full Backup' 
 WHEN @backup_type = 'D' THEN 'Differential Backup' 
 WHEN @backup_type = 'T' THEN 'Transaction Log Backup' 
 WHEN @backup_type = 'S' THEN 'System (Master & MSDB) Backup'  
 END;

if @dbname<> ''
Begin
SET @dbname = ',' + @dbname + ','
end

-- SELECT @database_name = DB_NAME()
SET @DATEH = CONVERT(CHAR(8),GETDATE(),112) + REPLACE (CONVERT(CHAR(6),GETDATE(),108),':','')
SET @MACHINE = CONVERT(VARCHAR,serverproperty('MachineName'))
SET @Q = CHAR(39) -- ANSI value for a single quote

SELECT @MailProfileName = name
FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
WHERE name like '%EU-IT-SQL_Alerts%'

--------------------------------------------------------------------------------------------------------------------
-- Check whether xp_cmdshell is turned off via Surface Area Configuration (2005) / Instance Facets (2008)
-- This is best practice !!!!! If it is already turned on, LEAVE it on !!

-- turn on advanced options
EXEC sp_configure 'show advanced options', 1 reconfigure 
RECONFIGURE  

CREATE TABLE #advance_opt (name VARCHAR(20),min int, max int, conf int, run int)
INSERT #advance_opt
EXEC sp_configure 'xp_cmdshell' -- this will show whether it is turned on or not

IF (select conf from #advance_opt) = 0 -- check if xp_cmdshell is turned on or off, if off, then turn it on
BEGIN

set @XPCMDSH_ORIG_ON = 'N' -- make a note that it is NOT supposed to be on all the time

--turn on xp_cmdshell to allow operating system commands to be run
EXEC sp_configure 'xp_cmdshell', 1 reconfigure
RECONFIGURE
END
ELSE
BEGIN
 -- make a note that xp_cmdshell was already turned on, so not to turn it off later by mistake
set @XPCMDSH_ORIG_ON = 'Y'
END

-- drop the temporary table to tidy up after ourselves.

IF EXISTS (
select * from tempdb.sys.objects
where name like '%advance_opt%'
)
BEGIN
drop table #advance_opt
END

--------------------------------------------------------------------------------------------------------------------

IF (@BACKUP_LOCATION <> ' ' 
and @job_name <> ' ' 
and @backup_type <> ' ' 
and @copy IN ('1','0')
and @production in ('Y','N')
and @INCSIMPLE in ('Y','N')
and @freq in ('Weekly', 'Daily'))

BEGIN

DECLARE backup_databases CURSOR FOR
 
-- ###################################
-- FOR DEBUG to check databases found
-- ###################################

-- declare @backup_type varchar(1)
-- declare @dbname varchar(300)
-- declare @INCSIMPLE varchar(1)
-- declare @ignoredb varchar(1)

-- set @INCSIMPLE = 'Y'
-- set @backup_type = 'F'
--set @dbname = '<your database name here>'
--set @ignoredb = 'N'

--if @dbname<> ''
--Begin
--SET @dbname = ',' + @dbname + ','
--end

-- ######### 

-- Recovery model 
-- 1 = FULL
-- 2 = BULK_LOGGED
-- 3 = SIMPLE
  
select 
d.name,
m.mirroring_role
from sys.databases d
inner join sys.database_mirroring m
on d.database_id = m.database_id
-- Full Database backups
--where d.state_desc <> UPPER('restoring') -- ignore any restoring databases
where d.state not in (1,2,3,6) -- ignore restoring, recovering, recovery pending and offline databases
and (d.source_database_id is NULL) -- ignore all database snapshots 
and (
-- Full Database Backups
(
@backup_type = 'F' and @dbname = ''
and
-- Pick up ALL SIMPLE mode db's apart from TEMPDB & MODEL, as well as ALL FULL mode databases
((d.recovery_model = 3 and d.database_id  NOT IN (2,3) and @INCSIMPLE = 'Y' 
or (d.recovery_model in (1,2) or m.mirroring_role <> 1)) -- ignore databases acting as a mirror
-- Pick up Master & MSDB in SIMPLE mode as well as ALL FULL/BULK-LOGGED mode databases
or (d.recovery_model = 3 and d.database_id  IN (1,4) or d.recovery_model in (1,2) and @INCSIMPLE <> 'Y') 
and d.source_database_id is NULL) -- ignore all database snapshots 
)
-- Differential backups -- ignore all SIMPLE mode databases
or (
@backup_type = 'D' and @dbname = ''
and
-- Pick up all other FULL recovery mode databases
(((d.recovery_model in (1,2) or m.mirroring_role <> 1)-- ignore databases acting as a mirror
-- Pick up all SIMPLE mode databases not including system db's
or (d.recovery_model = 3 and @INCSIMPLE = 'Y' and d.database_id  NOT IN (1,2,3,4)))
and d.source_database_id is NULL) -- ignore all database snapshots
)
-- Transaction Log Backups
or (
-- Pick up all other FULL/BULK-LOGGED recovery mode databases
@backup_type = 'T' and @dbname = ''
and
(((d.recovery_model = 1  
or d.recovery_model = 2) or m.mirroring_role <> 1 ) -- ignore databases acting as a mirror
and d.database_id  NOT IN (1,2,3,4)
and d.source_database_id is NULL) -- ignore all database snapshots
)
-- Master & MSDB only
or
(
-- Pick up MASTER & MSDB system databases only
@backup_type = 'S' and @dbname = ''
and (d.recovery_model = 3 and d.database_id  IN (1,4))
)
-- adhoc database backups
or 
(
-- Pick up only the database that has been passed into the procedure

--#################### COMMENTED OUT ON 01/09/2010 ###############################
--(@dbname = d.name
--and @dbname <> '') -- will only pick up a single database name passed into the procedure as a parameter
-- #### NEW ADDED  ON 01/09/2010 ####

-- added to ignore databases that are passed in as they are either backed up elsewhere or don't need backing up
--or (@dbname <> '' and NOT (CHARINDEX(',' + d.name + ',' , @dbname) > 0) and d.database_id  NOT IN (2,3) and @ignoredb = 'Y')
(@dbname <> '' and NOT (CHARINDEX(',' + d.name + ',' , @dbname) > 0) and d.database_id  NOT IN (2,3) and @ignoredb = 'Y')
-- added to only process multiple databases that are passed in as a parameter
or (@dbname <> '' and  (CHARINDEX(',' + d.name + ',' , @dbname) > 0) and @ignoredb = 'N')
-- #### NEW END ####
and 
(
((@backup_type = 'F'
or (@backup_type = 'T' and d.database_id  NOT IN (1,2,3,4)) -- ignore all system databases if backup type TX
or (@backup_type = 'D' and d.database_id  NOT IN (1,2,3,4)) -- ignore all system databases if backup type Diff
) or m.mirroring_role <> 1)
or (@backup_type = 'S' and d.database_id  IN (1,4)) -- ignore model and tempdb databases if backup type System
)
) 
)
order by d.name

-- Open the cursor.
OPEN backup_databases;

-- Loop through the update_stats cursor.

FETCH NEXT
  FROM backup_databases
  INTO @database_name, @mirrorrole;

print @@fetch_status

WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement failed or the row is beyond the result set
BEGIN

IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
BEGIN

set @dbname_dir = ltrim(rtrim(@database_name)) -- put the database name in another variable so it can be used in directory paths
select @database_name = '[' + @database_name + ']' -- bracket the database name to allow for unusual characters

-- ######################################################################

if @backup_type = 'D' -- Differential
or @backup_type = 'T' -- Transaction Log
begin
if not exists (
select * from msdb.dbo.backupset
where type = 'D' -- Database/Full Backup
and database_name = @dbname_dir -- database name without square brackets
and backup_finish_date  >= GETDATE()-7 -- there should be a full backup within 7 days of these types
)
Begin
set @orig_backup_type = @backup_type -- store original backup type
set @changed_backup_type = 'Y'
set @backup_type = 'F'
SET @full_backup_name = 'Full Backup' 

if @freq = 'Daily' -- We only want to do a WEEKLY FULL backup to fulfil our needs !!!!
begin 
set @orig_freq = @freq
set @freq = 'Weekly'
set @freq_changed = 'Y'
end
-- print 'Backup Type was changed here to ....' + @backup_type
end
end

-- ######################################################################
-- decide which file extension should be used based on the backup type that is passed into the procedure

if @backup_type <> ('T')
begin
if @backup_type = 'D'
begin
set @ext = '.diff'
end
else
begin
if @backup_type in ('F','S')
begin
set @ext = '.bak'
end
end
set @dir_type = 'SQL_Full'
end
else
begin
set @ext = '.trn'
set @dir_type = 'SQL_Trans'
end

-- not if azure......
IF @isCloud = 'N'
BEGIN

IF @production = 'Y'
or @production is NULL
or @production = '' -- if backup for a prod/systest/uat system then use folders per database name

begin

-- build up query string of file location in order to check for it's existence

If @isSP = 'Y'
Begin
set @subdir = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\SharePoint_DB\'
print @subdir
end
else
Begin
set @subdir = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\' + @dbname_dir + '\'
print @subdir
end

set @dir = 'dir ' + @subdir
--set @dir = @Q + 'dir ' + @subdir + @Q

print 'Directory structure is ..... ' + @dir

-- use xp_cmdshell to check existence of the required database named directory
CREATE TABLE #DirResults (Diroutput VARCHAR(500))
INSERT #DirResults
exec xp_cmdshell @dir  

-- if no directory of the required name is found, then create one
IF EXISTS (
select * from #DirResults where Diroutput like '%File Not Found%'  
or (Diroutput like '%The system cannot find the path specified%')
or (Diroutput like '%The system cannot find the file specified%')
)
BEGIN
print 'creating dir here ...'

-- re-define the @subdir variable using a \\ after the @backup_location to create a directory from that point onwards 

If @isSP = 'Y'
Begin
set @subdir = 'execute master.dbo.xp_create_subdir ' +'N' + @Q + @backup_location + '\' + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\SharePoint_DB' + @Q
end
else
Begin
set @subdir = 'execute master.dbo.xp_create_subdir ' +'N' + @Q + @backup_location + '\' + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\' + @dbname_dir + @Q
end

exec (@subdir); -- create directory structure
print @subdir
END

drop table #DirResults

-- ####################################################################
-- 11/03/2014
-- ####################################################################
If @isSP = 'Y'
Begin
SET @BACKUP_DB = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\SharePoint_DB\'  + @database_name + '_' + @DATEH + @ext
end
else
begin
SET @BACKUP_DB = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\' + @dbname_dir  + '\'  + @database_name + '_' + @MACHINE + '_BACKUP_' + @DATEH + @ext
end
end

-- ############################################################################################################################################
-- if for a development system, then use a generic area with NO specific database named directories, so db's can be added/removed as required.
-- ############################################################################################################################################

else
begin

-- build up query string of file location in order to check for it's existence

If @isSP = 'Y'
Begin
set @subdir = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\SharePoint_DB\'
print @subdir
end
else
Begin
set @subdir = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\'
print @subdir
end

set @dir = 'dir ' + @subdir

print 'Directory structure is ..... ' + @dir

-- use xp_cmdshell to check existence of the required database named directory
CREATE TABLE #DirResults2 (Diroutput VARCHAR(500))
INSERT #DirResults2
exec xp_cmdshell @dir 

-- if no directory of the required name is found, then create one
IF EXISTS (
select * from #DirResults2 where Diroutput like '%File Not Found%'  
or (Diroutput like '%The system cannot find the path specified%')
or (Diroutput like '%The system cannot find the file specified%')
)
BEGIN
-- re-define the @subdir variable using a \\ after the @backup_location to create a directory from that point onwards 
If @isSP = 'Y'
Begin
set @subdir = 'execute master.dbo.xp_create_subdir ' +'N' + @Q + @backup_location + '\' + @@SERVICENAME + '\' + @freq + '\SharePoint_DB' + @Q
end
else
Begin
set @subdir = 'execute master.dbo.xp_create_subdir ' +'N' + @Q + @backup_location + '\' + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + @Q
end

exec (@subdir); -- create directory structure
print 'creating dir .... ' + @subdir
END

drop table #DirResults2

--SET @BACKUP_DB = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\' +  @database_name +  '_' + @MACHINE + '_BACKUP_' + @DATEH + @ext

-- ####################################################################
-- 11/03/2014
-- ####################################################################

If @isSP = 'Y'
Begin
SET @BACKUP_DB = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\SharePoint_DB\'  + @database_name + '_' + @DATEH + @ext
end
else
begin
SET @BACKUP_DB = @backup_location + @@SERVICENAME + '\' + @freq +  '\' + @dir_type + '\' +  @database_name +  '_' + @MACHINE + '_BACKUP_' + @DATEH + @ext
end
end
END

if @isCloud = 'Y'
BEGIN
Set @BACKUP_DB = @backup_location +  @database_name +  '_' + @MACHINE + '_BACKUP_' + @DATEH + @ext
END


PRINT 'Backing up ... ' + @database_name;
PRINT @BACKUP_DB

BEGIN TRY

--  format correct syntax for type of backup specified

IF @backup_type in ('F','D', 'S')
BEGIN
set @sql = 'BACKUP DATABASE ' 
END;
ELSE
BEGIN
set @sql = 'BACKUP LOG '
END;
print @sql

-- If SQL 2012 or 2014 and we do not require cloud backups, OR the version is not
-- SQL 2012 or 2014, then...

IF ((LTRIM(RTRIM(@ver)) = 'SQL 2012' or  LTRIM(RTRIM(@ver)) = 'SQL 2014')
    and @isCloud = 'N')
OR 
   (LTRIM(RTRIM(@ver)) != 'SQL 2012' 
   and  LTRIM(RTRIM(@ver)) != 'SQL 2014')
BEGIN
IF @backup_type = 'D' -- if a differential backup
BEGIN
set @sql = @sql + @database_name + ' TO DISK='  + @Q + @BACKUP_DB  + @Q + 
' WITH DIFFERENTIAL, RETAINDAYS = 14, NOFORMAT, NOINIT'
END
ELSE
BEGIN
print 'here i am'
set @sql = @sql + @database_name + ' TO DISK='  + @Q + @BACKUP_DB  + @Q + 
' WITH RETAINDAYS = 14, NOFORMAT, NOINIT'
END
END
print @ver
print @iscloud

-- If SQL 2012 or 2014 and we require cloud backups, then...

IF ((LTRIM(RTRIM(@ver)) = 'SQL 2012'
or  LTRIM(RTRIM(@ver)) = 'SQL 2014')
and @isCloud = 'Y')
BEGIN
IF @backup_type = 'D' -- if a differential backup
BEGIN
set @sql = @sql + @database_name + ' TO URL = '  + @Q + @BACKUP_DB  + @Q + 
' WITH CREDENTIAL =' + @Q + @credential + @Q + 
', DIFFERENTIAL, NOFORMAT, NOINIT'
END
ELSE
BEGIN
--print @database_name
--print @backup_db
--print @credential
--print @SQL

print 'here i am in the azure bit'
set @sql = @sql + @database_name + ' TO URL = '  + @Q + @BACKUP_DB  + @Q +
' WITH CREDENTIAL =' + @Q + @credential + @Q + 
', NOFORMAT, NOINIT'
END
END;
-- SQL Server 2008 Enterprise Edition or SQL Server 2008 R2/SQL 2012 SE,DE or EE 
-- can backup using page level compression.

--BACKUP DATABASE AdventureWorks2012 
--TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak' 
--      WITH CREDENTIAL = 'mycredential' 
--     ,COMPRESSION
--     ,STATS = 5;
--GO 
IF LTRIM(RTRIM(@ver)) = 'SQL 2008'
BEGIN
IF @level = 'EE' 
or @level = 'DE'
BEGIN
set @sql = @sql + ', COMPRESSION'
END
END;
ELSE
-- amended to cater for SQL 2012 as this also uses compression for both 
-- Standard and Enterprise editions
IF LTRIM(RTRIM(@ver)) = 'SQL 2008 R2'  
or LTRIM(RTRIM(@ver)) = 'SQL 2012'
or LTRIM(RTRIM(@ver)) = 'SQL 2014'
BEGIN
IF @level != 'EX' -- not supported in Express version
BEGIN
set @sql = @sql + ', COMPRESSION'
END
END

-- if you want a copy only backup, it will be appended to the end of the backup command
IF @copy = 1 
BEGIN
set @sql = @sql + ', COPY_ONLY'
END;

-- ##############################################################################################################
-- 03/02/2014 -- checksum added
-- if you want a checksum for the backup, it will be appended to the end of the backup command
-- ##############################################################################################################

IF @checksum = 1 
BEGIN
set @sql = @sql + ', CHECKSUM'
END;

-- ##############################################################################################################

-- execute the backup command
print @sql;
exec (@sql);

END TRY

BEGIN CATCH

-- ##############################################################################################################
-- 14/03/2014 -- Now changed to email a list of recipients passed in at runtime, or if blank, to check for the 
-- failsafe operator and email these address
-- ##############################################################################################################

IF @recipient_list IS NULL 
or @recipient_list = ''
BEGIN

SELECT @recipient_list = email_address
FROM msdb..sysoperators
WHERE name = '<your operator to search for>' -- Name of main required operator

IF @recipient_list IS NULL
BEGIN

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
 N'AlertFailSafeOperator',
 @failsafe OUTPUT,
 N'no_output'

SELECT @recipient_list = email_address
FROM msdb..sysoperators
WHERE name = @failsafe
                             
END
END

PRINT @recipient_list

-- ##############################################################################################################

SELECT @ERR_MESSAGE = ERROR_MESSAGE(), @ERR_NUM = ERROR_NUMBER();
SET @MESSAGE_BODY='Error Backing Up ' + @database_name + '. Error Code ' + RTRIM(CONVERT(CHAR(10),@ERR_NUM)) + ' Error Message ' + @ERR_MESSAGE
SET @MESSAGE_BODY2='Failure of ' + @job_name + ' ' + @freq + ' ' + @full_backup_name + ' within ' + LTRIM(RTRIM(cast(@@SERVERNAME as VARCHAR(30)))) + '.' + @database_name
-- 11/03/2014 -- Added in a link to the SCOM console for LOR
SET @MESSAGE_BODY3='<your message here....>'
SET @MESSAGE_BODY = @MESSAGE_BODY + @MESSAGE_BODY3

PRINT @MESSAGE_BODY

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfileName,
@recipients = @recipient_list,
@importance = 'HIGH',
@body = @MESSAGE_BODY,
@subject = @MESSAGE_BODY2

-- on error within the procedure, check for existence of temporary tables
-- used by this procedure and delete them if they exist, just to be tidy!

IF EXISTS (select * from tempdb.sys.objects
where name like '%advance_opt%')
BEGIN
drop table #advance_opt
END

END CATCH

-- END

END -- end of @@fetchstatus if

-- if the backup type has been changed for the database that has just been processed,
-- then change the backup type back to it's original value and reset the other associated
-- variables.

if @changed_backup_type = 'Y'
begin
set @backup_type = @orig_backup_type
set @orig_backup_type = ''
set @changed_backup_type = 'N'

-- set the Full Backup name back to be what it was prior to the change 
SELECT @full_backup_name = CASE 
WHEN @backup_type = 'F' THEN 'Full Backup' 
WHEN @backup_type = 'D' THEN 'Differential Backup' 
WHEN @backup_type = 'T' THEN 'Transaction Log Backup' 
WHEN @backup_type = 'S' THEN 'System (Master & MSDB) Backup'  
 END;

-- set @freq back to it's original value
if @freq_changed = 'Y'
begin
set @freq = @orig_freq
set @orig_freq = ''
set @freq_changed = 'N'
end
end

FETCH NEXT FROM backup_databases INTO @database_name, @mirrorrole;

END

-- Close and deallocate the cursor.

CLOSE backup_databases;
DEALLOCATE backup_databases;

END;

ELSE

-- raise appropriate errors if parameters are left blank or incorrect values are given

BEGIN
IF @backup_location = ' '
BEGIN
raiserror('Parameter @backup_location must NOT be spaces', 16, 1);
END
ELSE
BEGIN
IF @job_name = ' '
BEGIN
raiserror('Parameter @job_name must NOT be spaces', 16, 1);
END
ELSE
BEGIN
IF @backup_type = ' '
BEGIN
raiserror('Parameter @backup_type must NOT be spaces', 16, 1);
END
ELSE
BEGIN
IF @copy not in ('0','1')
BEGIN
raiserror('Parameter @copy MUST be a value of ''1'' or ''0'')', 16, 1);
END
ELSE
BEGIN
IF @production not in ('Y','N')
BEGIN
raiserror('Parameter @production MUST be a value of ''Y'' or ''N'')', 16, 1);
END
ELSE
BEGIN
IF @freq not in ('Weekly','Daily')
BEGIN
raiserror('Parameter @freq MUST have a value of ''Weekly'' or ''Daily'')', 16, 1);
END
ELSE
BEGIN
IF @INCSIMPLE not in ('Y','N')
BEGIN
raiserror('Parameter @INCSIMPLE MUST be a value of ''Y'' or ''N'')', 16, 1);
END
ELSE
BEGIN
IF @ignoredb not in ('Y','N')
BEGIN
raiserror('Parameter @ignoredb MUST be a value of ''Y'' or ''N'')', 16, 1);
END
END
END
END
END
END
END
END
END

-----------------------------------------------------------------------------------------------------------------------
-- turn off advanced options

IF @XPCMDSH_ORIG_ON = 'N'  -- if xp_cmdshell was NOT originally turned on, then turn it off 
BEGIN

--  turn off xp_cmdshell to dis-allow operating system commands to be run
EXEC sp_configure 'xp_cmdshell', 0  reconfigure
RECONFIGURE

EXEC sp_configure 'show advanced options', 0 reconfigure
RECONFIGURE

 
END
-----------------------------------------------------------------------------------------------------------------------

END;