BackUp_DBs.sql

--Variables declaration
Declare @tck as varchar(6)                -- Ticket Number if you need
Declare @DbName as varchar(255)         -- variable to cursor
Declare @BackupDest AS varchar(255) -- variable to declare path to backup
Declare @UsrName as varchar(100)      -- Windows user who make backup
Declare @command as varchar(1000)     -- backup command
Declare @command2 as varchar(1000)    -- verification command
Declare @srv as varchar(150)            -- server name where databases are inside
--Set user variables
set @tck='XXXXXX'                             --only put the number of Ticket
--Set auto variables
set @UsrName=convert(varchar(150),REPLACE(SUSER_SNAME(),'\','.'))
set @srv=convert(varchar(150),ServerProperty('MachineName'))
--Cursor
DECLARE dbCursor CURSOR FOR select name from sysdatabases 
--Use next line if you want select all db's in server less system db's
--where name not in('master','Distribution','DataMirror' ,'tempdb','msdb','model')
--Use next line if you want to select one or more db's select for you
where name in('AdventureWorks')
open dbCursor
FETCH NEXT FROM dbCursor INTO @DbName 
    WHILE @@FETCH_STATUS = 0 
        BEGIN 
        set @BackupDest = 'D:\DBATEAM\' + 'ITG#' + @tck + '_srv_' + UPPER(@srv) + '_db_' + UPPER(@DbName) + '_date_' 
                            + CONVERT(VARCHAR(10),GETDATE(),10) + '_Dba_' + UPPER(@usrname) + '.BAK'
        set @command= 'backup database ' + @dbname + ' to disk=''' + @BackupDest+''' with copy_only'
        set @command2= 'restore verifyonly from disk=''' + @BackupDest + ''''
        --Output
        Print '*************************************************************'
        Print 'BackUp of Db ''' + UPPER(@srv) + '.' + UPPER(@DbName) + ''' performed by: ' + UPPER(@UsrName) + ' at ' + convert(varchar(25),getdate(),100)
        Print 'BakUp Source ' + @BackupDest
        Print ''
        Print 'System SQL message: ' 
        Print ''
        --Execute commands
                exec (@command)
        Print ''
        Print 'BACKUP VERIFICATION Procedure :'
                exec (@command2)
        print '*************************************************************'
        Print ''
FETCH NEXT FROM dbCursor INTO @DbName 
END
close dbCursor
deallocate dbCursor