CREATE procedure [dbo].[usp_BackupAllDB]

@databasename varchar(50)=null,

@backupdir varchar(100) =null

as

if @backupdir is null

set @backupdir='G:\sqldata\mssql\backup'

set nocount on

declare @dbname varchar(50),

@stime varchar(20),

@fname varchar(100),

@cmd varchar(200),

@sql varchar(1000)

if @databasename is not null

declare mycursor cursor for

select name

from master..sysdatabases

where name=@databasename

else

declare mycursor cursor for

select name

from master.sys.databases

where database_id not in (2,3)

and state_desc ='ONLINE'

order by name

open mycursor

fetch next from mycursor into @dbname

while @@fetch_status =0

begin

select @cmd = "exec master..xp_cmdshell 'if not exist "+@backupdir+"\"+@dbname+ " md "+@backupdir+"\"+@dbname+"'"

--select @cmd

exec (@CMD)

select @stime=replace(replace(replace(convert(varchar(19),getdate(),120),':',''),' ',''),'-','')

select @sql=" backup database "+@dbname +" to disk='"+@backupdir+"\"+@dbname+"\"+@dbname+"_"+@stime+"_D.BAK' WITH COMPRESSION"

exec (@sql)

select @sql

fetch next from mycursor into @dbname

end

close mycursor

deallocate mycursor

set nocount off