Link to home
Create AccountLog in
Avatar of Mehawitchi
Mehawitchi

asked on

What is the best practice for maintaing an up-to-date copy of a Transction Database

Hello there,

I have a massive database on SQL Server 2005, with some tables exceeding 100 million records.

Basically, I need to maintain an exact copy of that database for reporting purposes.

I'm aware of the Replication and Mirroring concepts, but I thought I  just seek your professional advice on how to simply copy that database and have an automated procedure for updating the copy, whenever the original database is updated.

The database copy will reside on the same server and it is meant only for reporting purposes.

Appreciate any advice on the above.

Thanks and regards,

Hani
Avatar of Aneesh
Aneesh
Flag of Canada image

Hello Mehawitchi,

If it is on the same server, why do you need another copy ? you can use the same db


GOD Bless,
Aneesh R.
Avatar of Mehawitchi
Mehawitchi

ASKER

Thanks aneeshattingal: for your reply.

It's just for safety reasons.

I'm not really comfortable with users quering my main database. Plus, what if I needed to update the database. the update is usually masive and involves bulk-inserting around 180,000 records in each upload.

Thanks again
If it is on the same instance, then just create a database snapshot...

http://msdn2.microsoft.com/en-us/library/ms175158.aspx
Thanks chapmandew

Any suggestions as to how to automate the updating of this snapshot, whenever the original databse gets updated.

Thanks again,

Hani
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks chapmandew

Greatly appreciated