Backup all the databases of SQL Server Instance
Code to backup all the databases of SQL Server Instance using the features of SQL Server like compression.
Code is written in order to check the version and use the compression intelligently.
Declare @C1 Cursor
Declare @path nvarchar(250)
Declare @name nvarchar(100)
Declare @dt nvarchar(14)
Declare @sql1 nvarchar(250)
Declare @sql2 nvarchar(250)
Declare @sql3 nvarchar(250)
set @path='D:\testing\'
set @C1=CURSOR For
select distinct name from sys.databases where name not like 'tempdb' and state_desc like 'ONLINE'
open @c1
set @dt= (select CONVERT(varchar(10),getdate(),112)+replace(CONVERT(varchar(10),getdate(),108),':',''))
fetch next from @c1 into @name
while @@FETCH_STATUS=0
Begin
if (@@VERSION like '%2000%' or (@@VERSION like '%2005%') or (@@version like '%Express%'))
Begin
Print 'Starting database backup '+@name
set @sql1='backup database '+@name+' to disk ='''+@path+@name+'_'+@dt+'.bak'' with stats=1,description=''backup database before patching activity'''
Print(@sql1)
Exec (@sql1)
Print @name+' Database backup completed successfully'
Print '#####################################################'
End
Else
Begin
Print 'Starting database backup '+@name
set @sql1='backup database '+@name+' to disk ='''+@path+@name+'_'+@dt+'.bak'' with stats=1,compression,description=''backup database before patching activity'''
Print(@sql1)
Exec (@sql1)
Print @name+' Database backup completed successfully'
Print '#####################################################'
End
Fetch next from @c1 into @name
End
close @C1
deallocate @c1
