ALTER PROCEDURE dbo.utl_BackupPrimaryDatabaseFileGroup (
	@DB nvarchar(12), 
	@Destination nvarchar(150) = '\as2-backup1\sql_Backups\',
	@InclIndexes bit
) AS
/*
	--------------------------------------------------------------------------------------------------------------
	Purpose     :	Creates FULL Backups of a specified DB, backing up only the PRIMARY filegroup
	Department  :	DB&R
	Created For :	DBA	
	Notes		:	@DB = Database to backup, @Destination = Location on the network
					Adapted/Improved from Author  : Richard Doering
					Web - http://sqlsolace.blogspot.com
					@InclIndexes = Sets whether or not to include the NCIndex file group
	--------------------------------------------------------------------------------------------------------------
	Created On  :	2/9/2012
	Create By   :	MyDoggieJessie
	--------------------------------------------------------------------------------------------------------------
	Modified By:	
	Modified On:	
	Changes:
		#1	
	--------------------------------------------------------------------------------------------------------------
	exec utl_BackupPrimaryDatabaseFileGroup 'SANDBOX', '\as2-backup1\sql_backups\', 0
*/
DECLARE @Server				nvarchar(25) = @@SERVERNAME
DECLARE @TimeStamp			nvarchar(27)
DECLARE @Random				int
DECLARE @UBound				int = 9999999
DECLARE @LBound				int = 1000000
DECLARE @intCounter			int
DECLARE @intMaxId			int
DECLARE @CurrentFile		nvarchar(1000)
DECLARE @ErrNo				varchar(15)
DECLARE @ErrMsg				varchar(2000)
DECLARE @Recipients			varchar(200)
DECLARE @Subject			varchar(175)
DECLARE @Body				varchar(MAX)

SET @Destination = @Destination + @Server + '\\'
SET @intCounter = 1
SET @Random = ROUND(((@UBound - @LBound -1) * RAND() + @LBound), 0)
SET @TimeStamp =  REPLACE(REPLACE(REPLACE(CONVERT(varchar(30),GETDATE(),20),'-',''),':',''),' ','_') 
	+ '_' +CAST(@Random AS nvarchar(7))


/* Make sure the Destination directory is valid and exists */
BEGIN TRY
	EXECUTE master.dbo.xp_create_subdir @Destination
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#Files%')
   DROP TABLE #Files

/* Create table to Store Potential Backups */   
CREATE TABLE #Files (
	id INT IDENTITY(1,1), 
	command VARCHAR(1000)
)

IF @InclIndexes = 1
	BEGIN
	INSERT INTO #Files (command) 
		SELECT 'BACKUP DATABASE [' + @DB +'] FILEGROUP = ''' 
			+ fg.name + ''' TO DISK = N''' + @Destination 
			+ @DB +  '_'  + @TimeStamp + '_' + fg.name +'.bak'''
			+ ' WITH NAME = ''' + @DB +  '_'  + @TimeStamp + '_' + fg.name +'.bak'''
			+ ', NO_COMPRESSION,  STATS = 10' 
		FROM sys.master_files mf
			JOIN sys.filegroups fg ON
				mf.database_id  = DB_ID(@DB) 
					AND mf.data_space_id = fg.data_space_id 
			JOIN sys.databases sd ON
				sd.database_id = DB_ID(@DB) 
			WHERE mf.type <> 1
				AND sd.state_desc = 'ONLINE'
				AND sd.recovery_model_desc = 'FULL'
				AND fg.name = 'NCIndexes'
		ORDER BY fg.data_space_id
	END
ELSE
	BEGIN
	INSERT INTO #Files (command) 
		SELECT 'BACKUP DATABASE [' + @DB +'] FILEGROUP = ''' 
			+ fg.name + ''' TO DISK = N''' + @Destination 
			+ @DB +  '_'  + @TimeStamp + '_' + fg.name +'.bak'''
			+ ' WITH NOFORMAT, NOINIT, NAME = ''' + @DB +  '_'  + @TimeStamp + '_' + fg.name +'.bak'''
			+ ', SKIP, REWIND, NOUNLOAD, NO_COMPRESSION,  STATS = 10' 
		FROM sys.master_files mf
			JOIN sys.filegroups fg ON
				mf.database_id  = DB_ID(@DB) 
					AND mf.data_space_id = fg.data_space_id 
			JOIN sys.databases sd ON
				sd.database_id = DB_ID(@DB) 
			WHERE mf.type <> 1
				AND sd.state_desc = 'ONLINE'
				AND sd.recovery_model_desc = 'FULL'
				AND fg.name <> 'NCIndexes'
		ORDER BY fg.data_space_id
	END
	
SELECT @intMaxId = MAX(ID) FROM #Files 
WHILE (@intCounter <= @intMaxId)
BEGIN
	SELECT @CurrentFile = command FROM #Files WHERE id = @intCounter 
	/* Perform the backup */

	BEGIN TRY
		PRINT  (@CurrentFile)
		SET @intCounter = @intCounter + 1
		EXEC sp_executeSQL @CurrentFile
	END TRY
	
	BEGIN CATCH
		SELECT @ErrNo = ERROR_NUMBER(), @ErrMsg = ERROR_MESSAGE()
		SET @ErrMsg = 'ERROR :: ' + @ErrNo + CHAR(13) 
		SET @Subject = 'ERROR :: Cannot backup the ' + @DB + ' database on ' + UPPER(@Server) + '!'
		SET @Body = 'There was an error backing up the ' + @DB + ' database files:' + CHAR(13) 
			+ '-----------------------------------------------------------------------------------' + CHAR(13) 
			+ @CurrentFile + CHAR(13) 
			+ '-----------------------------------------------------------------------------------' + CHAR(13) 
			+ @ErrMsg
			EXEC msdb.dbo.sp_send_dbmail  
				@recipients = @Recipients,
				@copy_recipients = 'woof@ssc.com', 
				@subject = @Subject,
				@body = @Body,
				@profile_name = 'DoggieMail',
				@body_format = 'TEXT',
				@importance = 'High'	
	END CATCH
END
/*
	exec utl_BackupPrimaryDatabaseFileGroup 'SANDBOX', '\as2-backup1\sql_backups\'
*/
GO