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.
sp_rename
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.sp_recompile
andsp_refreshview
respectively to mark them for recompilation.