USE [master]
   GO
   DECLARE @estimatedBackSizeMB INT
   DECLARE @estimatedDriveFreeSpaceMB INT
   DECLARE @dbCheckMessage varchar(80)
   DECLARE @enoughSpaceForBackupFlag INT
   DECLARE @drvLetter VARCHAR (5)
   SET NOCOUNT ON
   set @drvLetter = 'C'
   SET @dbCheckMessage = concat ('Checking database ', DB_NAME ())
   PRINT @dbCheckMessage

   SELECT @estimatedBackSizeMB = round (sum (a.total_pages) * 8192 / SQUARE (1024.0), 0)
   FROM sys.partitions p
   JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
   LEFT JOIN sys.internal_tables it
      ON p.object_id = it.object_id

   CREATE TABLE #freespace (drive VARCHAR (5), MBFree DECIMAL (8, 2))

   INSERT INTO #freespace (
      Drive,
      MBFree) EXEC xp_fixeddrives

   SELECT @estimatedDriveFreeSpaceMB = MBFree 
   FROM #freespace
   WHERE drive = @drvLetter

   IF @estimatedBackSizeMB * 1.15 < @estimatedDriveFreeSpaceMB
      SET @enoughSpaceForBackupFlag = 1
   ELSE
      SET @enoughSpaceForBackupFlag = 0
/*
   SELECT DatabaseName = db_name(),	
      Estimated_Back_Size_MB = @estimatedBackSizeMB,
      Estimated_Drive_Free_Space_MB = @estimatedDriveFreeSpaceMB,
      EnoughSpaceForBackupFlag = @enoughSpaceForBackupFlag
*/
   DROP TABLE #freespace

   IF @enoughSpaceForBackupFlag = 1
   BEGIN
   PRINT 'Continue to Backup...'
   DECLARE @path NVARCHAR(256); -- Path for backup files
   DECLARE @fileName NVARCHAR(256); -- Filename for backup
   DECLARE @fileDate NVARCHAR(20); -- Used for file name
   DECLARE @name NVARCHAR(50); -- Database name

   -- Build the path and file name.
    SET @path = 'C:\Temp\'
	SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    SET @name = DB_NAME ()
    SET @fileName = @path + @name  + '_' + @fileDate + '.BAK'
	  -- Backup the database.
    BACKUP DATABASE @name TO DISK = @fileName WITH  COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
      -- Loop to the next database.
    END
ELSE 
   PRINT 'Drive Space Problem...'



   SET NOCOUNT OFF