Hi,

Currently, the Database Maintenance Plans is backing up the database to
disk in "complete" mode every morning. I would like to do add a
diferential backup every 30 minutes appended to the same file. (ie.
DB200211110645.BAK)

Can anyone tell me how can script this? The issues are the name used and
periodicity.

TIA

Report this post to a moderator

You would need to script this and set it up as a scheduled job.
Specifically, you can create a VBScript which:

creates a FileScriptingObject
reads the file directory
finds the most-recent file with a .BAK extension and stores it in a variable
connects to SQL Server via DMO
executes the backup using the file name stored in the above variable

HTH

--
Tom

----------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

---
Tom Fu wrote in message <3DCFF00F.5090408@lycos.spam.com>...
Hi,

Currently, the Database Maintenance Plans is backing up the database to
disk in "complete" mode every morning. I would like to do add a
diferential backup every 30 minutes appended to the same file. (ie.
DB200211110645.BAK)

Can anyone tell me how can script this? The issues are the name used and
periodicity.

TIA

Report this post to a moderator

Are you sure that you want a differential backup.

For something like this you would normally do a daily full backup, and a
periodic transaction log backup ....

Have a look at maintenance plans.

"Tom Fu" <tomf@lycos.spam.com> wrote in message
news:3DCFF00F.5090408@lycos.spam.com...
> Hi,
>
> Currently, the Database Maintenance Plans is backing up the database to
> disk in "complete" mode every morning. I would like to do add a
> diferential backup every 30 minutes appended to the same file. (ie.
> DB200211110645.BAK)
>
> Can anyone tell me how can script this? The issues are the name used and
> periodicity.
>
> TIA
>

Report this post to a moderator

Steven Wilmot wrote:
> Are you sure that you want a differential backup.
>
> For something like this you would normally do a daily full backup, and a
> periodic transaction log backup ....
>

The database I am using is in "Simple" recovery mode, so AFAIK the
transaction log does not contain the latest changes. Thus, the need of
doing differential backups.

Any other possibilities besides Tom Moreau solution? I wonder if it is
possible to do something using TSQL instead of VBScript and the job
scheduler from the OS.

Thanks

Report this post to a moderator

Changing the database from simple to full logging would allow you to do
proper transaction logs.

(Unless you have a particular reason to avoid this method)

The job(s) can be scheduled under TSQL, but are much easier to manipulate
under Maintenace Plans ... VBScript is definitely NOT the ideal solution.

"Tom Fu" <tomf@lycos.spam.com> wrote in message
news:3DD12368.7070704@lycos.spam.com...
>
> Steven Wilmot wrote:
> > Are you sure that you want a differential backup.
> >
> > For something like this you would normally do a daily full backup, and a
> > periodic transaction log backup ....
> >
>
> The database I am using is in "Simple" recovery mode, so AFAIK the
> transaction log does not contain the latest changes. Thus, the need of
> doing differential backups.
>
> Any other possibilities besides Tom Moreau solution? I wonder if it is
> possible to do something using TSQL instead of VBScript and the job
> scheduler from the OS.
>
> Thanks
>

Report this post to a moderator