Backup SQL Database


DECLARE  @DB_NAMELIST VARCHAR(512)
DECLARE  @DB_NAME VARCHAR(512)
DECLARE  @SQL VARCHAR(MAX)
DECLARE  @BackupFolderLocation VARCHAR(50)
DECLARE  @BackupExtension VARCHAR(50)
declare  @pos        int
declare  @nextpos    int
DECLARE  @$$errno 		int
DECLARE  @$$errmsg 		varchar(1000)
DECLARE  @$$severitylevel 	int	

SET @DB_NAMELIST = (SELECT DISTINCT STUFF((
			SELECT ',' + NAME
			FROM SYS.DATABASES
			WHERE LEN(OWNER_SID) > 1
			ORDER BY NAME
			FOR XML PATH('')
			), 1, 1, ''))

SET @BackupFolderLocation = 'C:\SQLBackup\'
SET @BackupExtension  = '.bak'
SET @DB_NAMELIST = LTRIM(RTRIM(@DB_NAMELIST))+ ','
SET @Pos = CHARINDEX(',', @DB_NAMELIST, 1)

IF REPLACE(@DB_NAMELIST, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @DB_NAME = LTRIM(RTRIM(LEFT(@DB_NAMELIST, @Pos - 1)))
			IF @DB_NAME <> ''
			BEGIN
				SELECT @SQL =  ('BACKUP DATABASE [' + @DB_NAME + '] TO DISK =''' 
					+ @BackupFolderLocation + @DB_NAME + @BackupExtension + 
					''' WITH INIT ;') 

				--PRINT @SQL
				EXEC (@SQL);
				
				select @$$errno = @@error
							
				if (@$$errno <> 0)
				begin
					select @$$errmsg = 'Error : Backup, SQL : ' + @SQL
   					goto ERROR
				end
			END
				
			SET @DB_NAMELIST = RIGHT(@DB_NAMELIST, LEN(@DB_NAMELIST) - @Pos)
			SET @Pos = CHARINDEX(',', @DB_NAMELIST, 1)
		END --end while
	END
	
	GOTO END_PROC
	
	ERROR:
	 select @$$errmsg = 'Error dbo.sp_backup_db : ' + isnull(@$$errmsg,'')
	 raiserror (@$$errmsg, @$$severitylevel, 1)
	
	END_PROC: