Cacher is the code snippet organizer for pro developers

if not OBJECT_ID('dbo.Proc_BackupDatabaseWeeklyDifferential') is null 
  Drop Proc dbo.Proc_BackupDatabaseWeeklyDifferential
go

Create Proc dbo.Proc_BackupDatabaseWeeklyDifferential(
	@DbName sysname,
	@TargetPath nvarchar(1000)) as



Declare @FileName nvarchar(1000)
Declare @DirPath nvarchar(1000)
Declare @Sql nvarchar(2000)
Declare @Exists int
Select @DirPath = @TargetPath + '\' + cast(datepart(yyyy,getdate()) as nvarchar(10)) + cast(DATEPART(ww, getdate()) as nvarchar(20))
Select @FileName = @DirPath + '\' + @DbName + '.bak'

Select @Sql = 'backup database ' + QUOTENAME(@DbName) + ' to disk=''' + @FileName + ''' with compression, '
exec xp_create_subdir @DirPath
exec xp_fileexist @FileName, @Exists OUT

Select @Sql = @Sql + case when (not @DbName like 'master') and @Exists = 1 then 'differential' else 'format' end

EXEC sp_executesql @Sql
go

if not OBJECT_ID('dbo.Proc_BackupAllDatabasesWeeklyDifferential') is null
	Drop Proc dbo.Proc_BackupAllDatabasesWeeklyDifferential
go	

Create Proc dbo.Proc_BackupAllDatabasesWeeklyDifferential(
	@TargetPath nvarchar(1000)) as

Declare RS  Cursor Local Read_Only for Select name from sys.databases where state = 0 and name<>'tempdb'
Declare @DbName sysname
Open RS
fetch next from RS into @DbName
while @@FETCH_STATUS = 0 
begin
	Exec dbo.Proc_BackupDatabaseWeeklyDifferential @DbName, @TargetPath
	fetch next from RS into @DbName
end
Close RS
Deallocate RS

GO