BackUp Quastions

[acko bogicevic]

Hi everyone
I have the folowing situations with my backups.
With Database Maintenance Plan is created backup strategy. Full Backup
is performing every day at 1.00 am. And Romove files older than: is
checked on 2 days, and it's working fine.

I am interested how can i perform this action with tsql, without using
DMPlan.
I tryed this:
BACKUP DATABASE TEST TO DISK 'C:...' WITH INIT, RETAINDAYS = 2

I put this statement into job but it is not working. I tryed after that
with (WITH NOINIT) but in that case .bak file grow and files older than
2 days are not deleted.

How can i delete files older then 2 days.
Thanks

acko

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: BackUp Quastions


"acko bogicevic" <aconi2002@yaho o.com> wrote in message
news:408ccc86$0 $200$75868355@n ews.frii.net...[color=blue]
> Hi everyone
> I have the folowing situations with my backups.
> With Database Maintenance Plan is created backup strategy. Full Backup
> is performing every day at 1.00 am. And Romove files older than: is
> checked on 2 days, and it's working fine.
>
> I am interested how can i perform this action with tsql, without using
> DMPlan.
> I tryed this:
> BACKUP DATABASE TEST TO DISK 'C:...' WITH INIT, RETAINDAYS = 2
>
> I put this statement into job but it is not working. I tryed after that
> with (WITH NOINIT) but in that case .bak file grow and files older than
> 2 days are not deleted.
>
> How can i delete files older then 2 days.
> Thanks
>
> acko
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

RETAINDAYS means that the backup set cannot be overwritten for that number
of days - it does not remove backups. One way to do this in SQL is using a
process something like this:

1. Generate a different backup filename for each day, based on the date -
BACKUP will accept a variable for the destination file name
2. After running BACKUP, execute 'dir /b' with xp_cmdshell to get a list of
the files in your backup folder into a temp table
3. Using a cursor, parse the filenames in the table to get the date of each
backup
4. If a file is older than @x days (compare using DATEDIFF()), then use
xp_cmdshell again to delete it

But this is quite awkward to do in SQL, and may become complicated when you
have different backup types, different folders for each database's backups
etc. It would be probably easier to write an external script using Perl,
VBScript etc. which uses the SQLDMO objects to do everything. Or perhaps a
combination of both - use a multi-step job, where some steps are external
scripts, and some are SQL commands.

Simon

[Greg D. Moore \(Strider\)]

Re: BackUp Quastions


"Simon Hayes" <sql@hayes.ch > wrote in message
news:408d4a4e$1 _2@news.bluewin .ch...[color=blue]
>
> "acko bogicevic" <aconi2002@yaho o.com> wrote in message
> news:408ccc86$0 $200$75868355@n ews.frii.net...[color=green]
> > Hi everyone
> > I have the folowing situations with my backups.
> > With Database Maintenance Plan is created backup strategy. Full Backup
> > is performing every day at 1.00 am. And Romove files older than: is
> > checked on 2 days, and it's working fine.
> >
> > I am interested how can i perform this action with tsql, without using
> > DMPlan.
> > I tryed this:
> > BACKUP DATABASE TEST TO DISK 'C:...' WITH INIT, RETAINDAYS = 2
> >
> > I put this statement into job but it is not working. I tryed after that
> > with (WITH NOINIT) but in that case .bak file grow and files older than
> > 2 days are not deleted.
> >
> > How can i delete files older then 2 days.
> > Thanks
> >
> > acko
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it![/color]
>
> RETAINDAYS means that the backup set cannot be overwritten for that number
> of days - it does not remove backups. One way to do this in SQL is using a
> process something like this:
>
> 1. Generate a different backup filename for each day, based on the date -
> BACKUP will accept a variable for the destination file name
> 2. After running BACKUP, execute 'dir /b' with xp_cmdshell to get a list[/color]
of[color=blue]
> the files in your backup folder into a temp table
> 3. Using a cursor, parse the filenames in the table to get the date of[/color]
each[color=blue]
> backup
> 4. If a file is older than @x days (compare using DATEDIFF()), then use
> xp_cmdshell again to delete it
>
> But this is quite awkward to do in SQL, and may become complicated when[/color]
you[color=blue]
> have different backup types, different folders for each database's backups
> etc. It would be probably easier to write an external script using Perl,
> VBScript etc. which uses the SQLDMO objects to do everything. Or perhaps a
> combination of both - use a multi-step job, where some steps are external
> scripts, and some are SQL commands.
>[/color]


I'm going to suggest a much easier and in my mind more robust way.

Use the backupfile, backupset, etc tables in the msdb database.

This will let you find the proper filenames, dates, etc.

[color=blue]
> Simon
>
>[/color]