Dynamic Database Backup

-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
-- This procedure was written to facilitate the backing up of the databases on this server. 
-- Many times new databases are created and the backup procedures forgotten to be 
-- modified to include the new databases. This procedure will back up the databases
-- to the specified root folder/instancename/database/data with a name of
-- DatabaseName_YYYY_MM_DD_HH_MM_SS.bak 
--
-- This procedure requires DB Mail to be configured to receive error notifications.
-- The profile name i use is 'SQL Backup Mail'
--
-- Author: Mitchell Spruill
-- 6/22/2011
-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE usp_BackupDatabases
AS
BEGIN
-- construct a working table to hold all the databases from sys.databases
DECLARE @DBTable as 
TABLE
(
RowId int Identity(1,1),
DatabaseName varchar(150)
)

set nocount on

DECLARE @BackupRoot varchar(100)
DECLARE @DatabaseName as varchar(100)
DECLARE @RowId as int
DECLARE @RowCount as int
DECLARE @BackupPath varchar(500)
DECLARE @DateTime varchar(25)
DECLARE @InstanceName varchar(50)
DECLARE @DataPath varchar(100)
DECLARE @ProductVersion int
DECLARE @EngineEdition int
DECLARE @SQL as nvarchar(500)
DECLARE @R2 as bit
DECLARE @Body as nvarchar(max)
DECLARE @BackupType as nvarchar(5)
DECLARE @Start as datetime
DECLARE @End as int
DECLARE @Recipients as varchar(1000)
DECLARE @Profile_name as varchar(1000)
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
----- Must be set based on individual needs
----------------------------------------------------------------------------------------------------------------------------------------------------
SET @BackupRoot = N'Q:'  -- everything is below this point.
SET @Recipients = 'sqldba@somecompany.com'
SET @Profile_name = 'SQL Backup Mail'
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------

SET @R2 = CASE  WHEN CHARINDEX(' R2 ', @@VERSION) > 0 then 1 else 0 end
SET @EngineEdition = CAST(SERVERPROPERTY('EngineEdition')  as int)
SET @ProductVersion = CAST( LEFT(CAST(SERVERPROPERTY('ProductVersion') as varchar),
CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') as varchar)) -1) as int)
SET @DateTime=replace(replace(replace(convert(varchar(25), getdate(), 120),'-','_'),':','_'),' ','_')
SET @RowCount = (select COUNT(*) from @DBTable)
SET @InstanceName = ISNULL(CAST(SERVERPROPERTY('INSTANCENAME') as varchar),'Default')
SET @BackupType = 'Data'

-- Insert into the working table all the databases on this server
-- excluding databases like model, tempdb
insert into @DBTable(DatabaseName) 
SELECT  name FROM    sys.databases 
WHERE state = 0 -- online
AND name NOT IN (N'model', N'tempdb') -- exclude these databases
ORDER BY name -- by putting them in order we can track the backup progress on the file system.


-- select the top database in the table and start the backup
SELECT top 1 @RowId=RowId,@DatabaseName=DatabaseName from @DBTable
WHILE (@RowCount > 0)
BEGIN

-- data path = root + instancename + databasename
SET @DataPath=@BackupRoot + '\' + @InstanceName + '\' + @DatabaseName + '\' + @BackupType

-- backup path = datapath + databasename + time stamp
SET @BackupPath = @DataPath + '\' + @DatabaseName   + '_' +  @DateTime +'.Bak' 

-- if directory does not exist the create it
EXEC master.dbo.xp_create_subdir @DataPath

-- build sql statement
SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''' + @BackupPath + ''' WITH INIT'
 
-- if SQL Server 2005 use this
IF (@ProductVersion >= 9 AND (@EngineEdition = 3 or @R2 = 1))
begin
SET @SQL = @SQL + ', FORMAT, CHECKSUM, STOP_ON_ERROR'
end

-- if SQL Server 2008 Enterprise edition or 2008 R2  and not encrypted then use compression 
IF (
@ProductVersion >= 10 AND 
(@EngineEdition = 3 or @R2 = 1) AND
NOT EXISTS  (Select 1 from sys.databases where name = @DatabaseName and is_encrypted = 1)
)
begin
SET @SQL = @SQL + ', COMPRESSION'
end

-- execute the sql statement
BEGIN TRY
EXECUTE sp_executeSQL @SQL
END TRY
BEGIN CATCH
SET @Body = '[Error Message]' + char(13) + char(10) + ERROR_MESSAGE()
+ char(13) + char(10) + char(13) + char(10) 
+ '[Database]' + char(13) + char(10) + @DatabaseName
+ char(13) + char(10) + char(13) + char(10) 
+ '[Backup Drive]'+ char(13) + char(10)  + @BackupRoot
+ char(13) + char(10) + char(13) + char(10) 
+ '[Instance Name]' + char(13) + char(10) + @InstanceName
+ char(13) + char(10) + char(13) + char(10) 
+ '[SQL Statement]'  + char(13) + char(10) + @SQL

EXEC msdb.dbo.sp_send_dbmail  
@importance='HIGH',
@profile_name=@Profile_name,
@recipients=@Recipients,
@subject=N'Database backup error',
@body=@Body
END CATCH

-- remove from working table
delete from @DBTable where RowId=@RowId

-- get next database to backup
SELECT top 1 @RowId=RowId,@DatabaseName=DatabaseName from @DBTable

-- decrement counter
Set @RowCount = @RowCount - 1
END

set nocount off
END