Option to switch Off Creating .bak file on ALTER TABLE
The .bak file created by the SQL Alter Table takes a long time to produce and is 99% of the time redundant as we make backups before running this command. Can we have a switch to inhibit this function? I do not want the function totally removed as I can see that it would have its occasional use.
PLJ
PhilipLJackson
shared this idea
To alter a table we have to create a new copy of the table. We don’t spend any time creating the .bak file, that’s just the original file renamed.
4 comments
-
PhilipLJackson
commented
Jeremy, I have created an example for you, in the best environment (ie like I have at home) it took 35 seconds to perform the restructure, on the worst environment (ie like most of my customers have) it took 3:52 minutes to restructure. Basically the issue appears to be arround local server on a slow network.
Regards
PLJ
-
jeremym1234
commented
Philip, can you send me the structure of the table and the operations you are trying (for example, added a new field, added an index, etc? Perhaps it is restructuring then creating an index or something along those lines. A simple ALTER TABLE will not take 2 steps.
-
PhilipLJackson
commented
Can you please check that the statement "its just the original file renamed" because it is horrendously slow, if you watch in Data Architect then there appears to be 2 sql scripts running after each other, the first generates the .bak file (takes an age) and the second re structures the table. I will do sometimings if you wish
Regards
PLJ -
Rodd Graham
commented
As an alternative, introduce the BAK extension in front of the true file type instead of after. Example: TableName.BAK.ADT
If you did this minor change, then the bak file could be cleaned up, accessed, or (re)moved using system procedures already in place.
