Taking Backup Of All DBs

CREATE PROCEDURE [dbo].[USP_BACKUPDATABASES]
AS

SET NOCOUNT ON
BEGIN
BEGIN TRY


DECLARE @backup_path nvarchar(4000);
DECLARE @dbname nvarchar(max);
--DECLARE @recovery_model varchar(30);
DECLARE @sql nvarchar(4000);
DECLARE @backupfile nvarchar(4000);
DECLARE @BackupDirectory NVARCHAR(4000);
DECLARE @SERVERNAME SYSNAME;

-- Reading the default backup location from registry.

EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',  
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ;  
SET @backup_path = (SELECT @BackupDirectory AS [SQL Server default backup Value]) + '\'
--select @backup_path

Declare @len INT, @i INT, @Left varchar(max), @right varchar(max)
SET @SERVERNAME = (SELECT @@servername);
--SELECT @SERVERNAME AS [SERVER NAME];

IF @SERVERNAME like '%\%'
BEGIN

SET @Left= (select Left(@SERVERNAME, charindex('\', @SERVERNAME) - 1))
--SELECT @Left AS [DEFAULT SERVER NAME]

SET @len = (SELECT LEN(@SERVERNAME))

--SELECT @LEN
SET @right = (SELECT SUBSTRING(@SERVERNAME,Charindex ('\', @SERVERNAME)+1,@len))
--SELECT @right AS [SQL INSTANCE NAME]

END
ELSE
BEGIN
    SELECT @left  = @SERVERNAME
--SELECT @Left AS [DEFAULT SERVER NAME]
SELECT @right = 'default'
--SELECT @right AS [SQL INSTANCE NAME]
END

-- Determine if BACKUP COMPESSION is available based on SQL Server Version and Edition

DECLARE @CompressYN CHAR(1);
DECLARE @verinfoTbl TABLE
(
Verinfo sql_variant,
SPinfo sql_variant,
EditionInfo sql_variant
)
INSERT INTO @verinfoTbl (Verinfo, SPinfo, EditionInfo)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition');

IF (
 -- Check for SQL 2014
 -- Editions that support backup compression:
 -- Enterprise, Business Intelligence, Standard
 ((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '12.%') AND 
(
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%') 
 )
 ) 
 OR
(
 -- Check for SQL 2012
 -- Editions that support backup compression:
 -- Enterprise, Business Intelligence, Standard
 ((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '11.%') AND 
(
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%') 
 )
 ) 
OR 
(
 -- Check for SQL 2008 R2
 -- Editions that support backup compression:
 -- DataCenter, Enterprise, Standard
 ((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '10.50%') AND 
(
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%') 
 )
 ) 
OR 
(
 -- Check for SQL 2008
 -- Editions that support backup compression:
 -- Enterprise
 ((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '10.00%') AND 
(
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%') 
 )
 ) 
OR
(
 -- Check for SQL 2005
-- No SQL Edition supports compression
 ((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '9.00%') AND 
(
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
 ((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%') 
 )
 ) 
 
BEGIN
 SET @CompressYN = 'Y'
END
ELSE
---- BACKUP COMPRESSION not available
BEGIN
 SET @CompressYN = 'N'
END

-- Close all the opened Cursors.
IF (SELECT Cursor_status('global', 'c')) >= -1 
                  BEGIN 
                      IF (SELECT Cursor_status('global', 'c')) > 
                         -1 
                        BEGIN 
                            CLOSE c 
                        END 

                      DEALLOCATE c 
                  END

-- Get the names of all qualifying databases
DECLARE c CURSOR FOR 

SELECT name
FROM sys.databases 
WHERE state_desc = 'ONLINE'
--AND is_read_only  1
AND source_database_id is null /* Non-NULL = ID of the source database of this database snapshot.*/AND name NOT IN ('tempdb')
ORDER BY name;



OPEN c
FETCH NEXT FROM c INTO @dbname

-- Backup each database

WHILE (@@FETCH_STATUS  -1)

BEGIN
       -- Create the backup output file name

    SET @backupfile = 

        -- Backup path
        @backup_path

        -- Add the db name
        +  @dbname
+  '_'
+ '[' + @left +']'
+ '_'
+  '[' + @right +']'
+ '_'
+'_'
        -- Add the date and time to the file name
        + CONVERT (varchar, GETDATE(), 112) + '_'   -- date
        + REPLACE (LEFT (CONVERT (varchar, GETDATE(), 108), 5), ':', '_')   -- time

-- SELECT @backupfile
       BEGIN
              IF @CompressYN = 'Y'
              BEGIN
              SET @sql = 'BACKUP DATABASE' + quotename(@dbname) + ' TO DISK = ''' + @backupfile + '.BAK'' WITH INIT, COMPRESSION;'
  -- select @sql
              --PRINT @sql
               EXEC (@sql)
       END
       ELSE
       BEGIN
              SET @sql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @backupfile + '.BAK'' WITH INIT;'
  select @sql
              --PRINT @sql
              EXEC (@sql)
  
        END
END
              
    -- Move on to the next database
FETCH NEXT FROM c INTO @dbname

END

CLOSE c
DEALLOCATE c
END TRY

 BEGIN catch 
        DECLARE @ErrorNumber INT; 
        DECLARE @ErrorSeverity INT; 
        DECLARE @ErrorState INT; 
        DECLARE @ErrorLine INT; 
        DECLARE @ErrorProcedure NVARCHAR(4000); 
        DECLARE @ErrorMessage NVARCHAR(4000); 

        SELECT @ErrorNumber = Error_number(), 
               @ErrorSeverity = Error_severity(), 
               @ErrorState = Error_state(), 
               @ErrorLine = Error_line(), 
               @ErrorProcedure = Error_procedure(); 

        SELECT @ErrorMessage = 
               N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' 
               + 'Message: ' + Error_message(); 

        SELECT @ErrorMessage AS [Error_Message]; 

        SELECT @ErrorProcedure AS [Error_Procedure]; 

        PRINT 'Error ' 
              + CONVERT(VARCHAR(50), Error_number()) 
              + ', Severity ' 
              + CONVERT(VARCHAR(5), Error_severity()) 
              + ', State ' 
              + CONVERT(VARCHAR(5), Error_state()) 
              + ', Procedure ' 
              + Isnull(Error_procedure(), '-') + ', Line ' 
              + CONVERT(VARCHAR(5), Error_line()); 

        PRINT Error_message(); 
    END catch 
SET NOCOUNT OFF
END