2

Here is my situation: restored a known good backup (works fine on other machines) that originated on Sql Server 2012 onto a local 2012 install. Created DB as part of the restore. No errors reported.

I am accessing it through a Node.js app, using the Node-SqlServer driver (if that makes a difference). The first time that the app tries to access the db after restore, the db goes into Suspect mode.

I tried deleting the Db restoring from scratch. Same thing.

I was able to "fix" the db by running these commands. After that, the db runs fine (app can access and query the db witout issue). However, this seems like a less than ideal scenario.

Any ideas about what could be causing this and how I might be able to prevent it?

Computer is a laptop with a single SSD. I ran chkdsk and returned a clean bill of health. Note: this has happened with more than one db restore, specifically to this machine. So the circumstantial evidence points to the machine as being at fault here.

Community
  • 1
Yaakov Ellis
  • 168
  • 6
  • 1
    What was the output of your CHECKDB operations? http://www.johnsansom.com/simplify-your-dbcc-checkdb-output/ – John Sansom Nov 28 '13 at 08:12
  • @JohnSansom no errors – Yaakov Ellis Nov 28 '13 at 08:18
  • @JohnSansom Just ran a fresh restore, and before doing anything (trying to access, fix, etc), ran CHECKDB. This time it shows errors. See here for output. Name of the db is 'Sinner2'. – Yaakov Ellis Nov 28 '13 at 08:21
  • 1
    So you have the backup of a corrupted DB. Every time you restore it, it will be corrupted. Restore does not do any sort of CHECKDB validation. – Remus Rusanu Nov 28 '13 at 08:56
  • @RemusRusanu and the script from here just fixes the corruption by itself? – Yaakov Ellis Nov 28 '13 at 09:04
  • 2
    You should have a closer look at DBCC CHECKDB documentation (http://technet.microsoft.com/en-us/library/ms176064.aspx for a start). The famous REPAIR_ALLOW_DATA_LOSS will result in data loss for pages found corrupted. This can affect quite a few rows and you will hardly have any control on it. It's the last remedy if all other tries failed. – KookieMonster Nov 28 '13 at 10:06
  • That makes sense, thanks for the help. If one of you would like to post an explanation of this as an answer, I would be happy to accept it. – Yaakov Ellis Nov 28 '13 at 10:23
  • 1
    There is no magic to really fix the corruption. Ideally, the original DB should be fixed (by applying a really known good backup) and then obtain a new backup for you. DBCC REPAIR is just a last resort, a desperate measure, and you have 0 guarantees that the result is correct, consistent or even usable. – Remus Rusanu Nov 28 '13 at 10:39

1 Answers1

4

Knowing that a CHECKDB returns errors right after restoring a backup, as stated by Remus Rusanu, you have a corrupted backup. As fixing this afterwards is usually much more painful then before, you should plan (or ask your DBA) to check the consistency of your databases on a very regular basis. I remember having anxious moments waiting for the results of a first DBCC CHECKDB for databases that have been in production for a few years, long before I started my job.

If you want to know more about corruption and DBCC, have a look at:

So check (sorry for this one) the health before, try to restore a valid backup, and if you have no other choice, try DBCC REPAIR_ALLOW_DATA_LOSS. But as stated before, you'll lose data and that can even make your database unusable.

KookieMonster
  • 2,037
  • 19
  • 18