Search code examples
c#linq-to-sqllocaldb

Database corruption

I am planning to use localdb for next project and thinking of making rotating backup of database (it will be detached normally and only attached when software is running). And I was thinking how to do automatic restore from backup. This required me to know if database is corrupted. And I can't find anything about this subject.

My guess, peoples don't care on that level about database integrity. Backup and maintenance is a job for IT, there are dozens of tools for SQL Express.

My database will be local on PC and not big. I want to make it very simple:

  • detect if database is corrupt (how?)
  • offer user an option to restore database from most recent backup (this is easy)

P.S.: perhaps I don't know something about sql express, localdb, linq-to-sql. This is why question is very generic.

After getting answer I'll go with simple options:

  • if database can't be opened and file exists - database is corrupted, offer restore (not exists - create empty database and do initial setup);
  • provide following options (if user noticed something unusual or get errors during update, etc.): to check database DBCC CHECKDB (0, REPAIR_REBUILD) (could be good to try first) or to restore.

Automatic detection seems costly. Rather keep weekly/montly backup for manual restoration (if that would be really necessary for old data) and create mdf-file backup every run to minimize loss to 1 day maximum (which in my case is totally fine for an abnormal situation like database corruption).

Solution

  • I am planning to use localdb for next project a

    You mean you plan to run a database that is explicitly not made for running in production on production?

    And I was thinking how to do automatic restore from backup. This required me to know if database is corrupted. And I can't find anything about this subject.

    Read SQL documentation. Backusp, restores are all doe using standard SQL Commands. Totally easy to do that.

    I want to make it very simple: detect if database is corrupt (how?)

    DBC Checkdb command. Can take a lot of time, though, depending on the database.

    perhaps I don't know something about sql express, localdb, linq-to-sql.

    This can be solved by reading the documentation. I would for example never use localdb on anything production.

    Read the documentation? It starts with the first line:

    Microsoft SQL Server 2014 Express LocalDB is an execution mode of SQL Server Express targeted to program developers.