Differential Backup Script

I would like to test if the day is Sunday (listed below),
if 'YES' then delete the files in the directory with following critieria
(E:\NewOrleans\*FULL.BAK) and create a new full backup file.
If 'NO' (Sunday) create check to see if a E:\NewOrleans\*FULL.BAK file
exist if 'NO' (E:\NewOrleans\*FULL.BAK) create a new full backup file,
if 'YES' (E:\NewOrleans\*FULL.BAK) then create differential backup.
Please help me modifiy the script listed below to meet the criterias listed
above.
Thanks,
declare @.yyyymmdd varchar(24)
declare @.var5 tinyint
select @.yyyymmdd = convert(varchar(24), getdate(),112) -- yyyymmdd
select @.var5 = datepart(dw,getdate())
if @.var5 = 1 -- Sunday
-- directory full backup listing
Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
-- Delete full backup listing
Exec master..xp_cmdshell 'del E:\NewOrleans\*FULL.BAK'
-- create full backup
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _FULL.BAK + "'" + "
WITH INIT")
-- create diff backup
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _DIFF.BAK + "'" + "
WITH DIFFERENTIAL")
Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
Volume in drive E is NWORLBD01A Dumps
Volume Serial Number is DRV-73B6
NULL
Directory of E:\NewOrleans
NULL
05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
1 File(s) 168,968,064 bytes
0 Dir(s) 295,359,264 bytes free
NULLHi
You may want to split the functionality into different procedures and use
the scheduler to have one job for Sundays and one job for the rest of the
w! If may be easier to follow the backup wizard to create these jobs.
The day of the w will depend on what DATEFIRST is set to, therefore you
should make sure that you set it or make the conditions cater for the
different days.
WIth the IF statement you can group statements to be run on Sunday with a
BEGIN and END statements e.g. (This is unchecked!)
SET DATEFIRST 7
if @.var5 = 1 -- Sunday
BEGIN
-- directory full backup listing
Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
-- Delete full backup listing
Exec master..xp_cmdshell 'del E:\NewOrleans\*FULL.BAK'
-- create full backup
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _FULL.BAK + "'" + "
WITH INIT")
-- create diff backup
END
ELSE
BEGIN
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _DIFF.BAK + "'" + "
WITH DIFFERENTIAL")
END
John
"Joe K." wrote:

> I would like to test if the day is Sunday (listed below),
> if 'YES' then delete the files in the directory with following critieria
> (E:\NewOrleans\*FULL.BAK) and create a new full backup file.
> If 'NO' (Sunday) create check to see if a E:\NewOrleans\*FULL.BAK file
> exist if 'NO' (E:\NewOrleans\*FULL.BAK) create a new full backup file,
> if 'YES' (E:\NewOrleans\*FULL.BAK) then create differential backup.
> Please help me modifiy the script listed below to meet the criterias liste
d
> above.
> Thanks,
> declare @.yyyymmdd varchar(24)
> declare @.var5 tinyint
> select @.yyyymmdd = convert(varchar(24), getdate(),112) -- yyyymmdd
> select @.var5 = datepart(dw,getdate())
> if @.var5 = 1 -- Sunday
>
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
> -- Delete full backup listing
> Exec master..xp_cmdshell 'del E:\NewOrleans\*FULL.BAK'
> -- create full backup
> EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _FULL.BAK + "'" + "
> WITH INIT")
> -- create diff backup
> EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _DIFF.BAK + "'" + "
> WITH DIFFERENTIAL")
>
>
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
>