Piecemeal Restore SQL 2005

  • 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

  • - 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

  • 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

  • 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

  • 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!

  • 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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • transaction replication should solve ur problem

  • 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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply