468,785 Members | 1,730 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,785 developers. It's quick & easy.

Automatic backup of MSDE database.

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

Jul 20 '05 #1
3 10312
On 2004-08-03, bryja_klaudiusz[at]poczta[dot]fm wrote:
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

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
Jul 20 '05 #2

"bryja_klaudiusz[at]poczta[dot]fm" <dl*@zmyly.pl> wrote in message
news:ce**********@nemesis.news.tpi.pl...
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


You could use sp_add_job (with sp_add_jobstep and sp_add_jobschedule) 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
Jul 20 '05 #3
"bryja_klaudiusz[at]poczta[dot]fm" <dl*@zmyly.pl> wrote in message news:<ce**********@nemesis.news.tpi.pl>...
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


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\backup\master.002 master.003
ren d:\mssql7\backup\master.001 master.002
ren d:\mssql7\backup\master.bak master.001
del d:\mssql7\backup\master.003

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

BACKUP DATABASE master TO DISK = 'd:\mssql7\backup\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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Tim Morrison | last post: by
5 posts views Thread by Igor Solodovnikov | last post: by
2 posts views Thread by nt | last post: by
1 post views Thread by john_20_28_2000 | last post: by
1 post views Thread by gcetti | last post: by
3 posts views Thread by apple | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.