1

I am new to a relatively large project using a Microsoft SQL server database with C# and the Entity Framework.

The current development process is like this:

  • Code some feature/intend to fix a bug
  • Start the application
  • Make some changes via GUI to the database testing the coded functionality/testing if the bug was fixed
  • In case of failure -> run SQL scripts to delete the complete database changes and repeat the whole procedure

I am wondering if there is any "test/developer"-mode for SQL databases that could automatically revert the applied changes. Basically, while the program is running, everything should be inserted/updated/deleted to the actual database, but afterwards all changes should automatically be reverted. Is my thought completely unreasonable? Is our procedure with the SQL scripts more or less state of the art for database development?

Thanks in advance!

Edit : We are using a developer database and the code first approach of Entity. It is still very annoying to me to run SQL scripts every time to keep my developer database "clean/usable/free from corrupt data". Also sometimes you code functionality that is supposed to work only once in production and coding that feature, requires you to test it a couple of times which means you need to reset the database once/times tested.

Improve this question
6
  • To make sure I understand what is being asked...you run test against live, and want to revert anything that occurs in these test after they are completed? Commented Sep 1, 2017 at 8:20
  • I'm not familiar with Entity Framework, but 'reverting changes on failure' is a job you can handle at a SQL Server level. It's transactional, so you can use learn.microsoft.com/en-us/sql/t-sql/language-elements/… transaction rollback on failure. Commented Sep 1, 2017 at 8:20
  • your "test/developer" mode should be a test/developer database which should be (at minimal) a structural mirror of whatever the live database has, along with any changes you're currently working on. Whenever these changes are ready to roll live, make the changes to the live database and push the (now known working) changes for the db and the code live. You should ideally never be testing anything in a production environment. Commented Sep 1, 2017 at 13:14
  • @Leonidas199x No, we run tests against a developer database, but still would like to revert everything afterwards. Commented Sep 4, 2017 at 9:00
  • @JonathonOgden Since we are using SQL scripts to delete the data. This is basically what we are doing already more or less, no? Commented Sep 4, 2017 at 9:01

1 Answer 1

Reset to default
2

Short answer: No you can not do that in SQL Server unless you backup everytime you want to make some changes to database and restore it when something went wrong!

Long answer: Developing and Testing on a production database is a bad idea. Developers must have their own database in order to develop and test their code against database. Also it's better to abstract database in your code in order to make your code unit-testable without needing a real database.

If you don't want to go with abstracting your database, You still need to work with a test database.

Using EntityFramework + SQL Server so you can use Code First approach to map database to code.

Also you can use Entity Framework Migrations to track changes in Data Definition Level.

And if you need your fresh database have some inital data, you can use seed method in database initializer.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for your answer. I should have mentioned a couple more details: We are not testing on a production database, but it is still annoying to revert changes to a development database. We already use the code first approach.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.