Unit tests should preferably be independent of external services, systems and files. The standard way to achieve this is to create mocks. A mock is an object that can be used in place of the real resource and act in a predictable way to ensure the tests always give the same result. I think that this is a perfectly valid approach for any external services that the system is integrated with. The problem for business applications is the database. Mocking the database by providing a mock implementation of the database access layer is a huge task. It is too huge to be worth the effort. Another approach that I often use is to use a database in the tests, but keep it unchanged through transactions.
When maintaining a system that is in production there are often issues that need to be solved by running custom SQL scripts directly in the production database. There can be different reasons for this:
- Updating a lookup table to add a new option to dropdown boxes, where there is no user interface for handling the table.
- Adjusting data in a way not permitted by the normal work flow.
- Reverting an incorrect operation performed by a user.
- Find the data that should be changed.
- Create the update statement and validate it
- Run the complete statement.