Friday, June 05, 2009

SQL Server backup and recovery: Idera SQL safe backup

Useful script to backup all databases on a server with Idera SQL safe

alter proc dbo.sp_backupalldb
as

declare @dbname as varchar(100)
declare @fname as varchar(255)
declare @rownum as int
SET @rownum = 1

declare @dbcount as int
select @dbcount = count(*)
    from sys.databases
    where name not in ('tempdb')

while @rownum <= @dbcount
begin

select @dbname = name from (
    select name, row_number() over (order by name) rownum
    from sys.databases
    where name not in ('tempdb') ) dbs
where rownum = @rownum

if @@rowcount = 0 RETURN

print @dbname

set @fname = 'D:\Backup\' + @dbname + '.safe'

print @fname

EXEC [master].[dbo].[xp_ss_backup]  
@database = @dbname,  
@filename = @fname,  
@backuptype = 'Full'

set @rownum = @rownum + 1

end

SQL Server backup and recovery: Idera SQL safe backup

No comments: