0

Did a good mistake today. And enterprise was down for 10 hrs. Microsoft sql server. Was editing a row via Views->Create a view. (I am a sysadmin not a db admin that's why).

Accidentally because of pressing enter, a new row was inserted with random values. Because of that the whole application stopped. This is such a weird thing for me.

Is there a way I can prevent this in future. I don't want to update by using query, because that's even more risky. What worse? I had not taken the backup of table. Can you share ms-sql server query to take backup and also a query to rename a table?

Another big mistake of mine was that I forgot to tail the logs to check what was happening in application logs once I inserted a new row. Today I made an enterprise down and I feel so trash today.

9
  • 5
    "Is there a way I can prevent this in future" - if taking the enterprise down for 10 hrs is important (and it certainly sounds like it is) then you need to fix the process. e.g. changes to prod databases should not be done adhoc in the GUI but by a release pipeline that deploys the (scripted) change through test environment(s) first. So the fault is with the process that allows people to do this sort of change direct to prod with no testing. It is almost inevitable at some point this will cause a problem. Commented Sep 14, 2023 at 10:19
  • But.. with CREATE VIEW how you have inserted a new row?Anyway, you have a lot of methods to try to minimize impact... testing environment, pipeline, begin tran.... rollback tran in SSMS, impersonate a user with less grants with EXEC AS, database snapshot and be aware that you are in a production environment
    – Jean Noel
    Commented Sep 14, 2023 at 19:16
  • @JeanNoel - It is possible. Though strange. "New view" opens the view designer. Add a table to it and select the columns. Click the "Execute SQL" option and you get much the same editable grid as if you had selected the Edit option on the table in the first place Commented Sep 14, 2023 at 19:34
  • 1
    why is updating by a query more risky? it is far better for repeatability and consistency than poking around in flaky at best Management Studio WYSIWYG GUIs. As others have said, creating / following a safer process here is key
    – SE1986
    Commented Sep 14, 2023 at 23:20
  • 1
    @achhainsan that should again be picked up by procedure - anything tested would hit this problem in a test environment. If no test environment is avaiable, getting into the habit of adding BEGIN TRAN at beggining of updates and manually committing when you are happy with the output should mitigate the no WHERE clause issue
    – SE1986
    Commented Sep 15, 2023 at 8:29

1 Answer 1

3

Write the necessary changes as SQL script, test it rigorously in testing environment and only then run them on production.

What worse? I had not taken the backup of table. Can you share ms-sql server query to take backup and also a query to rename a table?

Backups in SQL Server is topic you could write books about. And sadly, you can't backup single table in SQL Server. When I end up editing something on production, I usually "backup" the table by doing SELECT INTO.

SELECT *
INTO MyTable_Backup
FROM MyTable
2
  • There is no testing environment.
    – achhainsan
    Commented Sep 14, 2023 at 10:27
  • 9
    @achhainsan - this experience should be motivation to get one created then Commented Sep 14, 2023 at 10:30

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.