SQL Server database and safety approach

I've created a software that is supposed to synchronize data between two databases in SQL Server. The program is tested as much as I was able to do so while having a limited amount of data and limited 开发者_开发百科time. Now I need to make it run and I want to play that safe.

What would be the best approach to be able to recover if something goes wrong and database gets corrupted? (meaning not usable by the original program)

I know I can backup both databases each time I perform the sync. I also know that I could do point in time recovery.

Are there any other options? Is it possible to rollback only the changes made by the sync service? (both databases are going to be used by other software)

You probably have, but I suggest investigating the backup and recovery options available in SQL Server. Since you have no spec, you don't know how the system is going to behave against these changes, leaving you with a higher likelihood of problems. For this reason (and many other obvious reasons) I would want to have solid SQL backups/recovery process in place. Unfortunately Express isn't very good in automating this area, but you can run them manually before the sync.

At the very least, make everything transactional; a failure in your program should not leave the databases in a partially sync'd state.

Too bad you don't have a full version of SQL Server... then you might be able to use something like replication services and eliminate this program altogether? ;)