Post reply

Creating a Reporting Database

  • February 2, 2005 at 4:08 am

    #87577

    Hi all,

    I want to be able to do the following:

    • Backup production database (full backup)
    • Restore this backup to another server over an existing database (or drop the existing database first). This database may have connections which should be dropped
    • Execute a job / stored procedure to denormalise some data within the newly restored database.
    • Notify by email if something goes wrong.

    The above should be done automatically every day.

    Anyone had experience of doing this? How should I achieve it?

    Thanks

    Darren

     

  • February 2, 2005 at 5:23 am

    #540123

    A rough guide to this would be to create an SQL script, and kick it off by OSQL, embedded in a windows shell script (batch file).

    This gets around the problems of trying to do a restore when the transmission of the backup isn't quite complete yet.

    Backup the database to disk, using the usual syntax (check the books on line) by using OSQL to connect to the master server.

    Copy the backup to the destination.

    Create an SQL script to do the following:

    Configure the target database to be offline (read up on sp_configure and OFFLINE in books online).

    Restore the backup from the copy.

    Configure the database to be Online.

    Kick off the SP to denormalise the data you want processed.

    At each of the stages, check for errors, and if detected, use xp_sendmail to send you some mail (if you go that route of having mail sending).

    The timing can be done by using a windows scheduler.

  • February 2, 2005 at 6:54 am

    #540148

    It appears you are trying to create a data warehouse.  Have you considered using Analysis Services and instead of backup/restores create jobs to move only the new data (denormalizing in the process)?

    If the phone doesn't ring...It's me.

  • February 2, 2005 at 11:46 am

    #540230

    Our database contains 95% of current data that can be edited / updated after its been inserted, so we dont really fit the data warehouse model.

    The other 5% would fit and we are planning to use analysis services for this, but would probably take the data from the reporting database.

    Thanks for replying.

Viewing 4 posts - 1 through 3 (of 3 total)

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