TSQL - Script to backup a given database with a single line of code

/*

       -- Execution

              -- Specific database backup

exec dbo.spBackup 'DATABASE_NAME_HERE';

              -- ALL databases

              exec SQLTOOLBOX.dbo.spBackup 'ALL';

*/

ALTER procedure [dbo].[spBackup]

(

       @DBs as varchar(max)

) as

begin

      

       DECLARE @backupPath varchar(100);

              set @backupPath = 'X:\MSSQL\Backup\';

       declare @timestamp char(14);

       DECLARE cur Cursor

       FOR

             

                     select

                           name as DatabaseName

                     from

                           sys.sysdatabases

                     where

                           -- Avoid system databases

                           ([dbid] > 4) and ([name] not like '$')

                           -- Avoid databases in a state not ready for backup

                           and sid != 0x01

                           AND (STATUS & 1073741824) = 0 -- not shutdown

                           AND (STATUS & 32) = 0 -- not pre recovery

                           AND (STATUS & 128) = 0 -- not recovering

                           -- Avoid DQ and Sharepoint

                           and name not like 'DQS_%'

                           --and name not like 'SharePoint%'

                           and name not like 'WSS%'

                           -- No x databases (our standard for temp)

                           and name not like 'x%'

                           -- Testing

                           and ( name in ( select * from dbo.getTableFromCSV( @DBs )) -- This function converts a comma-separated list into a table.

                                  or rtrim(ltrim(lower(@DBs))) ='all')

                     order by name ;

       DECLARE @dbName varchar(100);

       DECLARE @backupQuery varchar(500);

       OPEN cur;

       fetch NEXT FROM cur INTO @dbName;

       while (@@FETCH_STATUS <> -1)

       BEGIN

              -- Timestamp string for file name

              -- YYYYMMDDHHNNSS

              set @timestamp

                     = cast( convert(varchar(20),GETDATE(),112) as varchar(8) )

                     + case when datepart(hh, getdate())<10 then '0' else '' end + cast(datepart(hh, getdate()) as varchar(2))

                     + case when datepart(n, getdate())<10 then '0' else '' end + cast(datepart(n, getdate()) as varchar(2))

                     + case when datepart(s, getdate())<10 then '0' else '' end + cast(datepart(s, getdate()) as varchar(2))

              ;

              --print(@timestamp);

              -- Backup SQL statement

              set @backupQuery =

                     'Backup database '

                           + @dbName

                     + ' to disk = '''

                           + @backupPath

                           + @timestamp

                           + @dbName

                           + '.bak'' '

                     + 'with '

                           + 'compression '

                           + ', format '

                     + ';'

              ;

              -- Print SQL statement

              print @backupQuery;

              -- Execute backup script

              EXEC( @backupQuery );

              -- Get next database

              Fetch NEXT FROM cur INTO @dbName;

       END

       CLOSE cur;

       DEALLOCATE cur;

      

       declare @subj as varchar(250)

       declare @comment as varchar(max)

       declare @prof as varchar (9)

      

       --set @comment='Saved in Box 2’s '+ @backupPath +'. Backing up Database ('+ @DBs + ') by: ' + suser_sname() + ' on : ' + cast(getdate() as varchar(100)) +'..|  Message sent from Box2'

       set @subj= @DBs + ' Database Backup  (Box 2)'

              set @prof = suser_sname();

              set @comment= @DBs + ' Database Backup' +' ON: ' + cast(getdate() as varchar(100)) + ' By: ' + @prof +'  '+'('+ suser_sname() +')' + ' Saved in SERVER NAME HERE.'

       EXEC msdb.dbo.sp_send_dbmail

       @profile_name=@prof,

       @recipients='EMAIL_1@HERE.com;EMAIL_2@HERE.com; ',

       @subject=@subj,

       @body=@comment ,

       @body_format='HTML'

;

end;