Technical Article

Differential Backup for DBs w/Full backup

,

This script will backup all databases that are online and have a recovery property of 'SIMPLE' AND already have a full backup. This script is set to run Monday thru Saturday at our site.

SET QUOTED_IDENTIFIER off
set nocount on
declare @dbname varchar(36),@cmd varchar(255)
declare dbname_cursor cursor
    for
select DISTINCT db.name from master..sysdatabases   db, msdb..backupset bs,msdb..backupmediafamily bf
where  db.name not in ('master','tempdb')
and bs.database_name = db.name and bs.type ='d' and bs.media_set_id =  bf.media_set_id
and  physical_device_name = 'E:\Program Files\Microsoft SQL Server\MSSQL\backup\' + convert(varchar(40),db.name) + '.bak'
order by db.name

open  dbname_cursor
fetch dbname_cursor into @dbname

while @@fetch_status = 0

begin
if DATABASEPROPERTYEX(@dbname,'Recovery') = 'SIMPLE' and DATABASEPROPERTYEX(@dbname,'Status') = 'ONLINE'
  begin
        select @cmd ='backup database '+@dbname+' to DISK="E:\Program Files\Microsoft SQL Server\MSSQL\backup\'+@dbname+'.bak" with noinit,differential'
           print @cmd
          execute (@cmd)
  end

fetch dbname_cursor into @dbname
end
close dbname_cursor
deallocate dbname_cursor

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating