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