• GonnaCatchIT (5/28/2016)


    I know that option, but want to know if SQL Server supports redirection to different database.

    Issue is we dont have control over the SQL servers and there are lot of internal restrictions in renaming the DB's.

    What kind of "restrictions"?

    Shifting gears and I don't mean this in an unkind fashion, it doesn't sound like this was a well thought out evolution. It sounds like some good manual effort was put into removing legacy data but there was no plan as to what to do with the legacy data nor any plan to prevent additional unwanted legacy data from become a problem in the future.

    It also sounds like this evolution wasn't actually tested.

    Whether or not you find some way other than renaming the database to accomplish your current goal, I strongly recommend that you stop what you (the team at your company) are doing and devise a plan for the automation of archiving legacy data in a separate database or databases. Since you already have code that stripped out the legacy data, a couple of simple tweaks to the code should be all that is necessary to move data out of your main database to other databases. If you do it correctly (for example, one database per month), you can even take a final monthly backup and set the databases to READ_ONLY so that you no longer have to back them up, maintain indexes, etc, etc. It would also allow you to easily drop entire months (just drop the database) when the individual months finally reach the end-of-life.

    And, no... if you have the Enterprise Edition and the subject of Partitioned Tables comes up, I do NOT recommend using partitioned tables for legacy data storage because you cannot restore just a month or two for DR or refreshing of data on Dev and Test systems (I just went through that ugly truth with a 500GB table we're forced to keep all the data "forever" by regulations). Partitioned Views across the monthly databases would be the way to go.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)