Automatic backup of MSDE database.

[bryja_klaudiusz[at]poczta[dot]fm]

Hi,

How to automate database backup (MSDE server v8.0)? Is some free tool
which can help on this or can I use some stored procedure? Plan:
Complete - 1 per week
Differential - 1 per day
--
*Best regards,*
Klaudiusz Bryja

[Trociu]

Re: Automatic backup of MSDE database.

On 2004-08-03, bryja_klaudiusz[at]poczta[dot]fm wrote:[color=blue]
> Hi,
>
> How to automate database backup (MSDE server v8.0)? Is some free tool
> which can help on this or can I use some stored procedure? Plan:
> Complete - 1 per week
> Differential - 1 per day[/color]
Some time ago MS published an Enterprise Manager 120 Days Trial. There you
can define the roles for SQL Agent - jobs that will be started some day at
specified hour.

Trociu

--
Plain-text over all!!!
/**********/
write me : trociu@autonom ict pwr wroc pl
search me : gg: 1382729

[Simon Hayes]

Re: Automatic backup of MSDE database.


"bryja_klaudius z[at]poczta[dot]fm" <dla@zmyly.pl > wrote in message
news:cenq7p$kfh $1@nemesis.news .tpi.pl...[color=blue]
> Hi,
>
> How to automate database backup (MSDE server v8.0)? Is some free tool
> which can help on this or can I use some stored procedure? Plan:
> Complete - 1 per week
> Differential - 1 per day
> --
> *Best regards,*
> Klaudiusz Bryja
>[/color]

You could use sp_add_job (with sp_add_jobstep and sp_add_jobsched ule) to add
a scheduled job to run the BACKUP command when you want. Alternatively, you
could create the job with SQLDMO, if prefer a COM-based approach. The
simplest approach might be to pay USD50 for the Developer Edition, or
download the trial version and get the full client tools that way, but I
have no idea if the licensing allows that or not, so you should check first.

Also see this link:

http://www.aspfaq.com/show.asp?id=2442

Simon

[cold_ronald]

Re: Automatic backup of MSDE database.

"bryja_klaudius z[at]poczta[dot]fm" <dla@zmyly.pl > wrote in message news:<cenq7p$kf h$1@nemesis.new s.tpi.pl>...[color=blue]
> Hi,
>
> How to automate database backup (MSDE server v8.0)? Is some free tool
> which can help on this or can I use some stored procedure? Plan:
> Complete - 1 per week
> Differential - 1 per day[/color]

Here's a skeleton process you can alter to suit your needs, with the
database up.

MSDE doesn't have maint. plans, but it comes with SQL Agent to
schedule things. Connect from Ent. Mgr. on another server and create
a job with two steps (if you don't have EM at all somwhere in your
shop, this won't work for you I guess). First step fires off an OS
command batch file on your target server that looks like this :

rem this file is used for the MSDE backup process zjec june '03
ren d:\mssql7\backu p\master.002 master.003
ren d:\mssql7\backu p\master.001 master.002
ren d:\mssql7\backu p\master.bak master.001
del d:\mssql7\backu p\master.003

Second step is a T-SQL step that looks like :

BACKUP DATABASE master TO DISK = 'd:\mssql7\back up\master.bak'


You can see I keep 3 versions. You will have to manually step through
the process as you are setting up the process if you want versions.
If you don't want versions, just issue the delete.

You would probably want two jobs, one for full, other for incr, or you
could get fancy with the scheduling on one job.

Alternately, use a free crontab scheduler to shut your db instance
down and use a similar process to copy the .mdb / .ldb to another
location.

HTH

cold_ronald