Instance databases backup

You sometimes need to backup a complete instance including the user and the system databases.
Plus if you are working with a cluster with several instances, you could need to move the backups on a local drive.
The following script will help you to do that and will create a folder on the setted volume, with you instance name.
I also use the compression and the COPY_ONLY option that allow to backup databases no interfering with the production backup plans you may have (Translog LSN management)

---------------------------------------
--	DECLARE VARIABLES
---------------------------------------
DECLARE @dbname			VARCHAR(50) -- database name  
DECLARE @BackupPath			VARCHAR(256) -- path for backup files  
DECLARE @fileName			VARCHAR(256) -- filename for backup  
DECLARE @fileDate			VARCHAR(20) -- used for file name 
DECLARE @FinalBackupDirectory VARCHAR(250) -- used for file name 

DECLARE @LOC			INT
DECLARE @VAR			VARCHAR(250)
DECLARE @FINAL			VARCHAR(250)
DECLARE @Command			VARCHAR(250)
DECLARE @InstanceName		VARCHAR(250)
DECLARE @ReturnCode			INT

DECLARE @BackupDrive		VARCHAR(250)
DECLARE @BackupDirectory	          VARCHAR(250)
DECLARE @FullDirectory		VARCHAR(250)

---------------------------------------
--	Variables definition
---------------------------------------
SET @BackupDrive = 'D'
SET @BackupDirectory = 'Backup'
SET @FullDirectory = 'Full'


-- The final name will look like
-- D:\Backup\InstanceName\DatabaseName\Full\DatabaseName.bak

---------------------------------------
--	Get the Date
---------------------------------------
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

---------------------------------------
--	ENABLE XP_CMDSHELL
---------------------------------------
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE

---------------------------------------
--	Create cursor with all Databases
--	to backup
---------------------------------------
DECLARE db_cursor CURSOR FOR  
	SELECT name
	FROM master.dbo.sysdatabases 
	WHERE name  NOT IN ('tempdb') -- Exclusions de bases

		---------------------------------------
		--	Create the final backup directory
		---------------------------------------
		SELECT @InstanceName = CONVERT(sysname, SERVERPROPERTY('instancename'));
			
		IF @InstanceName is NULL
			BEGIN 
				SET @InstanceName ='Default'
			END

			
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @dbname   

	WHILE @@FETCH_STATUS = 0   
	BEGIN   
	
		---------------------------------------
		--	Build the complete directory 
		---------------------------------------
		SET @FinalBackupDirectory = @BackupDrive +':\' + @BackupDirectory + '\' + @InstanceName + '\' + @dbname + '\' + @FullDirectory

		---------------------------------------
		--	Build the command to create 
		--	directory and execute it
		---------------------------------------
		BEGIN TRY
			SET @Command = 'md ' + @FinalBackupDirectory
			EXEC master.dbo.xp_cmdshell @Command;
		END TRY
		BEGIN CATCH
		END CATCH
		
		---------------------------------------
		--	If the backup directory is already 
		--	created, erase the content
		---------------------------------------
		BEGIN TRY
			SET @Command = 'del ' + @FinalBackupDirectory + '*.BAK'
			EXEC master.dbo.xp_cmdshell @Command;
		END TRY
		BEGIN CATCH
		END CATCH
		
			---------------------------------------
			--	Isolate the Database path
			---------------------------------------

			SET @LOC = 0

			SELECT @VAR = REVERSE(physical_name) from sys.master_files WHERE database_id = DB_ID(@dbname)
			AND UPPER(RIGHT(physical_name,3))='MDF';

			SET @LOC = CHARINDEX('\', @VAR)
			SET @VAR = REVERSE(@VAR)
			SET @BackupPath = LEFT(@VAR,LEN(@VAR) - @LOC +1)

			---------------------------------------
			--	Create backup database name
			---------------------------------------
		   SET @fileName = @BackupPath + @dbname + '_' + @fileDate + '.BAK' 

			---------------------------------------
			--	Current Database backup
			---------------------------------------
			BACKUP DATABASE @dbname TO DISK = @fileName  WITH COMPRESSION, COPY_ONLY


			---------------------------------------
			--	Current Database Checkup
			---------------------------------------
			SET @Command = 'RESTORE VERIFYONLY FROM DISK = N''' + @fileName + ''''

			EXECUTE (@Command)

			IF @@ERROR <> 0 
				RAISERROR('Error verifying SQL backup.', 16, 1) WITH LOG

			---------------------------------------
			--	Build the command to move 
			--	the backup and execute it
			---------------------------------------
			SET @Command = 'move ' + @fileName + ' ' + @FinalBackupDirectory
			EXEC master.dbo.xp_cmdshell @Command;

			---------------------------------------
			--	Go to the next database
			---------------------------------------
			FETCH NEXT FROM db_cursor INTO @dbname   

	END   

---------------------------------------
--	Deallocate Cursor
---------------------------------------
CLOSE db_cursor   
DEALLOCATE db_cursor 


---------------------------------------
--	DISABLE XP_CMDSHELL
---------------------------------------
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE