Fooling SQL Server :)

[DBAWinnipeg]

Good Morning everyone

Here's a neat little question for you

2 environments in question

Production and Reporting

Every night we want to take the transaction logs of production and restore it on the reporting server

BUT here is the kicker... there are views that we're going to add to our Reporting Server every night after this restore happens.

My question is this... I know that in order to use the transaction logs the databases HAVE to be in sync. Can you fool SQL Server into believing that the 2 environments are equal by removing those views before trying to restore from the transaction log... and then re-applying them after the fact?






Thanks in advance!!!

Colin in da 'Peg

 

[JohnDTampaBay]

IMHO, I would look into replication or log shipping. In replication, the fact that the target (Reporting) database has views that do not exist in the source (Production) database is irrelevant since you define exactly which articles (in this case, all tables) are to be replicated. There is an easier solution that backup and restore of logs. Just my two cents. Good luck!

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[nigelrivett]

It also has to be in standby - i.e. read only.

Why not hold the views to another database - in fact have views for all the tables in this new database and channel access through it. You can then take your time over applying logs or however you wish to update - you can flip flop between databases and swap over by just updating the views or renaming the databases.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.