Backup All Databases

-----------------------------------using cursor----------------------------------
declare @name varchar(50)

declare @filename varchar(50)

declare @path varchar(50)

declare @filedate varchar(50)



set @path='E:\'

select @filedate =convert(varchar,getdate(),112)

declare db_cursor cursor for

select name from sys.databases where name not in ('master','model','tempdb','msdb')

open db_cursor



fetch next from db_cursor into @name

while(@@fetch_status=0)

begin

set @filename=@path+@name+'_'+@filedate+'.bak'

backup database @name to disk =@filename

print @filename

fetch next from db_cursor  into @name

end

close db_cursor

deallocate db_cursor

-------------------------------using temp tables----------------------------------------------------
declare @name varchar(50)
declare @path varchar(50)
declare @filename varchar(50)
declare @filedate varchar(50)
declare @i int
declare @cnt int
declare @sql nvarchar(4000)
set @path ='E:\'
select @filedate = convert (varchar (20),getdate(),112)

create table #tab(id int identity ,name varchar(50))
insert into #tab select name from sys.databases where name not in ('master','model','msdb','tempdb')
set @i=1
select @cnt=count(1) from #tab

while (@i<=@cnt)
begin
select @name =name from #tab where id =@i
set @filename =@path+@name+'_'+@filedate+'.bak'
set @sql ='backup database '+ @name +' to disk ='+''''+ @filename +''''
exec sp_executesql @sql
set @i=@i+1
end

drop table #tab