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: