0

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).

Sinatr
  • 20,892
  • 15
  • 90
  • 319

1 Answers1

0

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.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • *Not made for running in production on production*. It will be a local PC software and I need to store some data, so using database is a good idea (was using mdb before), my colleague is using `localdb`, care to explain to us what we are doing wrong. Thanks for [`DBCC CHECKDB`](http://technet.microsoft.com/en-us/library/ms176064.aspx), it sounds like an answer to me! Have to test it first though. – Sinatr May 15 '14 at 11:38
  • Well, that is prodeuction and I would use - express at least. SOmething sensible. – TomTom May 15 '14 at 11:53