0

In an attempt to remove some downtime to my users, I want to redirect them to a back up table while I'm repopulating the production table. The tables in question have been in place for years so my plan was to rename the table and then create an synonym of the old table name with the idea that I could just backup the production table to the back up, point the synonym to the back up, truncate and reload the prod table and then point the synonym back to the prod table once done.

This was fine until I tried this on one of my older tables with enforced dependencies, I was renaming through SSMS by right clicking. I think I can force the rename using sp_rename but I dont have a proper matching test environment to test it in.

If sp_rename works, and I can then create the synonym with the original table name, would all the dependencies still work?

Note: Due to occasional data loading issues, we want to keep the backup table of data to copy back from incase of partial loads. I'm hoping that by redirecting the query traffic to a different table during the load, my loading issues greatly diminish.

3
  • 2
    What dependencies does it have? Relevant: sqlperformance.com/2021/09/sql-performance/… this is a much better solution to fast loading than sp_rename Jan 11, 2022 at 10:35
  • Mostly stored procs but a couple views as well. I had come across a question on here were the answer was to use SWITCH but being unfamiliar with it I'm hesitant to try it. Plus I'm already set up with rename for a handful of other tables and I'm not really looking to maintain two methods. I'm also not as worried about the time as these are overnight reloads and the impact on the users will be far less than it is today.
    – bl4met
    Jan 11, 2022 at 15:48
  • For views and stored procedures you probably need to recompile them. You can use sp_recompile and sp_refreshview respectively to mark them for recompilation. Jan 11, 2022 at 15:52

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.