July 25, 2008 at 8:34 am
Hi
I have a database A on LIVESERVER and a database B on DATAWAREHOUSE.
Both databases have the filegroups [PRIMARY], [CDR] and [BILLING].
What I want to achieve is an overnight Piecemeal restore process which backs up just the [CDR] and [BILLING] Filegroups on A, restore them on top of B - leaving the PRIMARY Filegroup on B as it is.
I then want to recover B so it is usable.
Is this possible, from books online seems not - Looks like you have to restore PRIMARY?
To achieve what I want - do I need to move all my tables I dont want to copy off [PRIMARY] to a new [FG] and then just backup and restore [PRIMARY], [CDR] and [BILLING]?
My tables currently on PRIMARY need to be replicated transactionally to the Datawarehouse. No data is created on the Warehouse - tables are read-only access, but an analyst needs to create stored procedures on B and for them not to be blown away over night.
Does anyone have a way round this or a better Datawarehouse Loading solution (outside of SSIS)?
Cheers
July 25, 2008 at 8:44 am
- Wouldn't you be better off setting up db-mirroring ?
(maybe even using db-snapshots to use the data you want)
It will keep up with the updates if both instances are able to make a quorum (connect to each other)
- maybe even use a full db-restore path. (and sync the users afterward)
- create another database to host your procedures and have the procedure using 3-way object naming (e.g. select col1 from otherdb.otherschema.othertable) or if you want to avoid that, just create views in your proc-hosting db and have those views pointing to the objects in then restored db.
create view myview
as
select *
from otherdb.theschema.theobject
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 25, 2008 at 8:47 am
Thanks for your suggestion,
DB Mirroring is not an option as I dont want the overhead on Server A - the load must be totally offline.
The reason I want to backup and restore the files [billing] and [cdr] is they are enormous tables that are created in batch.
All i want is them to be refreshed nightly on the Datawarehouse.
Cheers
Rich
July 25, 2008 at 8:55 am
Yuckon (7/25/2008)
Thanks for your suggestion,DB Mirroring is not an option as I dont want the overhead on Server A - the load must be totally offline.
The reason I want to backup and restore the files [billing] and [cdr] is they are enormous tables that are created in batch.
All i want is them to be refreshed nightly on the Datawarehouse.
Cheers
Rich
While updating my post, you've already replied. I added a last part regarding the procedudures.
Did you consider log-shipping ?
It all comes to a price !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 25, 2008 at 9:00 am
Log Shipping wont work, I want the UD functions and SPs to remain as they are on the database B.
These must be in the same DB if possible.
Sorry but I thought you guys on here like a challenge!
July 25, 2008 at 9:17 am
Yuckon (7/25/2008)
...Sorry but I thought you guys on here like a challenge!
This is not a challenge.
The point always is to find a thingy that works, alligned with what's reasonable, and suited to your purposes.
If you scratch all mechanismes provide to you by the suite, without willing to pay the price, that's up to you.
If I were in your shoes, I'd try to avoid linked servers (that's why I didn't propose that), I'd go fot the solution using db B to just host your procs,... (the things you desperatly want to keep :discuss: ) and just load the data into another ([piecemeal] restored db).
Just be sure you did split primary filegroup to just host your catalog !
and put everything else in suitable filegroups.
Then you can perform a piecemeal restore by just restoreng primary and the other two filegroups you need.
Give it a thought and you'll find a simple and sound way to do it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 25, 2008 at 9:29 am
Just a thought - all procs stored in syscomments right?
Is there a way to back these up in a script before overwriting [PRIMARY] and re deploy them after the load?
I know SQL2005 wont let you modify the Global catalog so this could scupper that plan.
July 25, 2008 at 10:22 am
Have you considered replication? Yes, there is a small overhead, but you can transfer the data realtime or on demand and you don't have to recreate or change the procedures/views/functions.
Plus, with replication the secondary is always online and available, unlike with mirroring, log shipping where the secondary is in standby/recovering.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2008 at 11:29 am
i'm in telecom as well
we replicate our CDR and billing data via SQL replication and the reporting people have their own databases and grab data from the replicated copies to make their reports
July 27, 2008 at 5:01 am
transaction replication should solve ur problem
July 27, 2008 at 9:23 am
Are you sure you want to move all data? Replication, while a small load on the LIVESERVER, is the least amount of data moving.
there are creative ways to do things. I'd recommend replication if it's a straight data move, table to table. If you transform things at all, and many warehouses will want to "cleanse" data, I'd use SSIS instead. Replication moves things more often, while yo can schedule SSIS.
Backup/restore, while you might make the piecemeal restore work (do Primary once), puts a huge load on the warehouse, pulling in all data when perhaps lots of it hasn't changed.
The stored procedure thing can be handled by reading through objects created or using a specific schema for analysts, scripting their work, reapplying it, but I wouldn't recommend backup/restore.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply