0

In our system, we suddenly lost all data. Noone could log into the system and the data was suddenly empty.

A closer look into the database showed, that there is now double the tables that there was before, just with another schema name in front of it instead of the standard DBO, it is not Timenord.tablename. All the data is still in the DBO tables, but the system is trying to use the new tables.

We havent been making any update to the system for a couple of days, so why this sudden behavior?

How do I fix it? Im using .net core 2.2, and i have never seen this issue before.

Dont even know how to tell the system to use the other schema.....

Smileyman
  • 47
  • 9
  • Has anything changed in the application code or config? – David Feb 15 '19 at 20:03
  • Temporary fix, set default schema: https://stackoverflow.com/questions/8208019/how-to-set-the-default-schema-of-a-database-in-sql-server-2005 – Brad Feb 15 '19 at 20:05
  • @David Nothing has changed. That's the strange part. If it had been part of an update or something I could better understand it. – Smileyman Feb 15 '19 at 20:13
  • @brad I will try that tomorrow morning. Should I just change the default scheme and then delete the rest of the tables? – Smileyman Feb 15 '19 at 20:14
  • I would research the root cause first or else it could just happen again. The default schema should look at a specific schema first (if you dont specify in your code like this dbo.TableName) so it will look for dbo. and if it finds the object should use that one. – Brad Feb 15 '19 at 20:23
  • 1
    Yeah that isn't something that "just happened". It is something running DDL and DML on your server. Is the data replicated to this server? Just grasping for straws in thin air. – Sean Lange Feb 15 '19 at 20:28
  • You can try checking int he default trace. [see here](https://www.sqlservergeeks.com/sql-server-how-to-query-for-ddl-changes-from-the-default-trace-when-no-ddl-auditing-has-been-configured-on-the-database/) and [here](https://www.mssqltips.com/sqlservertip/4057/capture-sql-server-schema-changes-using-the-default-trace/). This assumes you haven't restarted the sql server. – S3S Feb 15 '19 at 20:37
  • You're not using an entity framework app which has been configured to build a fresh database? – Dale K Feb 15 '19 at 22:41
  • 1
    @Brad Throw me an answer. Your suggestion with setting the default schema worked and i could then delete the extra tables from the database :-) Ty :-D – Smileyman Feb 20 '19 at 12:33

1 Answers1

0

From comments above, temporary fix at least till you can find the cause of the issue is to set default schema: How to set the default schema of a database in SQL Server 2005?

The default schema should look at a specific schema first (if you dont specify in your code like this dbo.TableName) so it will look for dbo. and if it finds the object should use that one.

Brad
  • 3,454
  • 3
  • 27
  • 50
  • Thinking, that setting default schema would solve it permanently as long as i dont change the database user being used by .net core :-) In .net Core, you can set the default schema in applicationDbContext class in your data folder :-) – Smileyman Feb 20 '19 at 12:39
  • That should fix your issue of it going to differrent schema if they all get created again, but I think should try to find out what happened, especially if it happens again. – Brad Feb 20 '19 at 12:42