restore strategies...

We have some fairly large sized databases (approx. 200
gigs), and one of the business requirements is to have the
data replicated over to the DRP server at least once a day.
Transactional Replication is not a favourable options as
many tables do not have primary keys and many others have
identity keys.
In order to minimize data and network throughput, I was
thinking of doing a full backup of these databases once
weekly, and differential backups everyday. Further, I
would restore these differential backups everyday to the
DRP server. However, I would still need to restore the
full database once weekly before I can continue to restore
any additional differential backups. Doing a full restore
would tax my throughput to a large extent... meaning, more
network activity, which would slow down the application.
Is there a way I can spruce up this process? Thanks for
all your suggestions in advance.> We have some fairly large sized databases (approx. 200
> gigs), and one of the business requirements is to have the
> data replicated over to the DRP server at least once a day.
Have you thought about Log Shipping for Disaster Recovery Plan?
You only need to restore transaction logs to the secondary database. the
network traffic and time could be minimized.
> Transactional Replication is not a favourable options as
> many tables do not have primary keys and many others have
> identity keys.
Is it possible to force PKs on these tables( eg. replacing unique clustered
indexes with clustered primary keys)?
You can also replicate tables with identity columns although it takes more
effort in tweaking this.
> In order to minimize data and network throughput, I was
> thinking of doing a full backup of these databases once
> weekly, and differential backups everyday. Further, I
> would restore these differential backups everyday to the
> DRP server. However, I would still need to restore the
> full database once weekly before I can continue to restore
> any additional differential backups. Doing a full restore
> would tax my throughput to a large extent... meaning, more
> network activity, which would slow down the application.
> Is there a way I can spruce up this process? Thanks for
> all your suggestions in advance.
There is a third-party backup tool from DBAssociates called "SQLLiteSpeed"
that can shrinks your 200GB db down to 20 GB or less. It has saved me much
time to backup and copy files.
Richard|||I have resently started using log shipping and database's
range from a few gigs to hundreds of gigs. My database is
not setup with any primary keys. Is there an issue with
log shipping and not having primary keys?
>--Original Message--
>We have some fairly large sized databases (approx. 200
>gigs), and one of the business requirements is to have
the
>data replicated over to the DRP server at least once a
day.
>Transactional Replication is not a favourable options as
>many tables do not have primary keys and many others have
>identity keys.
>In order to minimize data and network throughput, I was
>thinking of doing a full backup of these databases once
>weekly, and differential backups everyday. Further, I
>would restore these differential backups everyday to the
>DRP server. However, I would still need to restore the
>full database once weekly before I can continue to
restore
>any additional differential backups. Doing a full restore
>would tax my throughput to a large extent... meaning,
more
>network activity, which would slow down the application.
>Is there a way I can spruce up this process? Thanks for
>all your suggestions in advance.
>.
>|||Log Shipping is basically an automatic backup and restore process. So it has
nothing to do with whether you have PKs or not. As a general rule
a table should have a primary key.
"jnsland" <jlandstrom@.wausaufs.com> wrote in message
news:021801c3507e$7006a190$a401280a@.phx.gbl...
> I have resently started using log shipping and database's
> range from a few gigs to hundreds of gigs. My database is
> not setup with any primary keys. Is there an issue with
> log shipping and not having primary keys?
>
> >--Original Message--
> >We have some fairly large sized databases (approx. 200
> >gigs), and one of the business requirements is to have
> the
> >data replicated over to the DRP server at least once a
> day.
> >
> >Transactional Replication is not a favourable options as
> >many tables do not have primary keys and many others have
> >identity keys.
> >
> >In order to minimize data and network throughput, I was
> >thinking of doing a full backup of these databases once
> >weekly, and differential backups everyday. Further, I
> >would restore these differential backups everyday to the
> >DRP server. However, I would still need to restore the
> >full database once weekly before I can continue to
> restore
> >any additional differential backups. Doing a full restore
> >would tax my throughput to a large extent... meaning,
> more
> >network activity, which would slow down the application.
> >
> >Is there a way I can spruce up this process? Thanks for
> >all your suggestions in advance.
> >.
> >|||::::::::::::::::
::::::::::::::::
>There is a third-party backup tool from DBAssociates
called "SQLLiteSpeed"
>that can shrinks your 200GB db down to 20 GB or less. It
has saved me much
>time to backup and copy files.
I've used and like this product, however, our business
unit thinks that this is an expensive tool though I did
try to explain to them that it will be cost beneficial
over the long run. Well... guess we'll have to live with
this bottleneck. Thanks for your suggestions though.