Restore Database daily without SP's

[M A Srinivas]

Hello all,

I have the following requirement (SQL 2000, SP2)

1. Need to restore production database(A) to another database (B) on
another server (No direct connection)
2. In the restored back-up(B) SP codes should not be visible (even to
sa) or all SP's can be deleted .

3. This needs to be carried out daily

4. New tables may be added frequently to A

5. This job should be scheduled

6. I can overwrite the entire database (B)

7. I can not encrypt SP in the original database (A)


I thought of Snap-shot replication first. But when new tables are
added , I need to add new tables manually to publication .

Can some one suggest most reliable and implementable method ?

Thanks
Srinivas

[Greg D. Moore \(Strider\)]

Re: Restore Database daily without SP's


"M A Srinivas" <masri@vsnl.com > wrote in message
news:f7e90f78.0 307242125.3da82 cb8@posting.goo gle.com...[color=blue]
> Hello all,
>
> I have the following requirement (SQL 2000, SP2)
>
> 1. Need to restore production database(A) to another database (B) on
> another server (No direct connection)
> 2. In the restored back-up(B) SP codes should not be visible (even to
> sa) or all SP's can be deleted .
>[/color]

Hmm, a normal Backup/Restore won't do this (since it'll restore the stored
procs.)

You're probably better off writing a DTS package.

[color=blue]
> 3. This needs to be carried out daily
>
> 4. New tables may be added frequently to A[/color]

That might be difficult to handle with a DTS package automatically, but if
you make sure you add them manually you're all set.

Another option would be to have a SECOND DB and put the stored procs in
there and then just qualify all stored procs by prepending the name of the
second DB.

So, myProc_foo in the first database would be moved to the 2nd one and now
called SecondDB..myPro c_foo.

A little more complicated, but doable.

[color=blue]
>
> 5. This job should be scheduled
>
> 6. I can overwrite the entire database (B)
>
> 7. I can not encrypt SP in the original database (A)
>
>
> I thought of Snap-shot replication first. But when new tables are
> added , I need to add new tables manually to publication .
>
> Can some one suggest most reliable and implementable method ?
>
> Thanks
> Srinivas[/color]

[Erland Sommarskog]

Re: Restore Database daily without SP's

Greg D. Moore (Strider) (mooregr@greenm s.com) writes:[color=blue]
> Hmm, a normal Backup/Restore won't do this (since it'll restore the stored
> procs.)
>...
> Another option would be to have a SECOND DB and put the stored procs in
> there and then just qualify all stored procs by prepending the name of the
> second DB.[/color]

Aargh! Never hard-code DB-names! You get all sorts of trouble if you
need to set up a test environment on the same server.

However, a second DB was my thought to. First backup original DB. Restore
locally into a second database. Drop all stored procedures. Backup the
second database. Ship and restore on the second server.

If the disk space and time is available, this is probably the simplest
way.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp