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

1 comment:

Alexis said...

There are a lot of good tool for work with MS SQL Server.But I know one not bad tool-repair mdf.It helped me many times as far as I remember.Tool has free status and moreover it can can save recovered data as SQL scripts and supports data extraction via the local area network.