sql-serversql-server-2008-expressdata-lossterminal-server

What could be the reason for SQL Server Express to roll back a database to a previous state?


I have some very strange problems. I have an application running on Windows 2003 terminal server from multiple clients. The application uses SQL Server 2008 Express as its database.

Yesterday, I connected to the app, closed some sessions on the server that were not responding, and to my surprise, I saw that some data was missing from the database. After a futher search I found that all the database changes made from last week were lost.

It's like the database rolled back all the changes, and returned to the state of one week ago! I can confirm that all the changes were lost. In fact I have inserted a record into a table with identity_insert ON (to manually insert an ID on an autonumeric col) and that record is missing, so there is no way this is a program failure.

Does anyone have any idea of what could have happend here?

EDIT

I have a suspect: could a transaction initiated by a session stays in a unconfirmed state for one week, retain all the database changes and when I close the session rollback all the changes made?

EDIT II

Find this on log: Log


Solution

  • SQL Server never rolls back a database to a previous state (like this). The database was restored, or the entire disk/VM was rolled back, or DML was executed to create the impression that a rollback happened (but really didn't). Maybe someone executed a sync tool in the wrong direction.

    The question does not have information that allows for finding the problem. But it certainly isn't SQL Server rolling back a database.

    You can try examining the log using fn_dblog.