Hi there

Hope someone can help me with this. I have a number of databases located on
one server which I need to replicate to a backup server on a fairly regular
basis.

My first thought on doing that was to use SQL replication however I have hit
upon some problems with that. The main one being that a number of the
tables are over the maximum field limit for SQL replication.

I am therefore looking for suggestions on how best to do this whilst
minimising any extra load put on the server. My thoughts have included
scheduling a regular backup on the source server and then writing a small
app which will copy the backup file from source to destination and then kick
off a restore command but there must be a better way.

Hope someone can help.

Steven

What do you mean by a fairly regular basis? Once a day, once a week, once
an hour etc? How large are the db's? If it is only once a day or the db's
are relatively small I would simply do as you stated and restore the full
backup. You can easily scipt a vb or tsql job to do this task. If it's is
more frequent you may want to look at Log shipping. You can find more
details in BOL.

--
Andrew J. Kelly SQL MVP


"Steven Clark" <sjc (AT) Junk (DOT) uksupport.net> wrote

Fairly regular is about every 30mins to an hour... we need this machine to
be reasonably up to date.

The databases are quite large... one is almost 1.6 gig and is growing about
100 meg a week. The other is nearly 400 meg but I can see the size of this
growing quickly shortly.

I'm just going off to have a look at log shipping.

Steven

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Sorry to be a pain guys.

Have just looked at log shipping and that looks ideal but I have a problem
in that we are only running SQL 2000 Standard edition and not enterprise.

Can any one advise of any way round this or does anyone have any more ideas
as to how this can be achieved.

Thanks

Steven

"Steven Clark" <sjc (AT) Junk (DOT) uksupport.net> wrote

You can still write your own log shipping routine and use it
with any edition - Enterprise Edition just has a wizard to
set it up for you. You can search the sql server newsgroups
using google to find some examples. You can also find more
information at:
http://www.sql-server-performance.co...g_shipping.asp
And the SQL Server 2000 Resource Kit has information and
scripts for setting up log shipping with it's version called
Simple Log Shipper.

-Sue

On Wed, 8 Sep 2004 00:35:03 +0100, "Steven Clark"
<sjc (AT) Junk (DOT) uksupport.net> wrote: