My SQL Server died and I can access the files on the C Drive but booting is a no go. Yesterdays backup is great but need today's data.

I found the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data with all my databases in there but I am unsure if that is all I need.

Please advise -I really do not want to get fired in the process.

Thank you.

  • Restore went well, copied the whole data folder over after renaming yesterdays to old_data, had to adjust security and off we went. Looks like everything is there. @Peter-K @Chopper3 @squillman Thank you so much for the help offered. I will talk to the chief to move it to a better location. Thanks again! – Jimmy Jul 6 '11 at 23:25

in default instalation, those files are all data you need. but if you have attached databases or you are using filestreams (off by default) or you have more instances, you must look for them too (*.mdf, *.ldf).

first make backup (copy) of them, then try to attach them to new sql server.

next time use backups, raid and data on other drive.

| improve this answer | |
  • I have lots of mdf and ldf files inside that data folder. – Jimmy Jul 6 '11 at 20:59
  • copy them all to another disk, and then attach yours databases (from sql, or management studio). you can also copy them to new sql server installation dir (mssql.1/mssql/data) but its better to keep them on another drive than system – Peter K Jul 6 '11 at 21:14

If you've got the .mdf files in that folder then copy them to the new server you build and reattach the databases. You'll also want to find the transaction log (.ldf) files as your databases were likely not closed in a clean state after suffering a hard shutdown. The folder you list is the default installation folder so likely they're in that folder along with the data files. You'll need these for the reattach. This will get your data back to the point where the server died, assuming there is no unrecoverable corruption.

If that fails then you'll need to restore from your good backup, but you'll only be able to get the data as of that backup. (I'm assuming it's a full backup here and your database is in simple recovery mode).

| improve this answer | |

If your job is on the line, get the go-ahead to hire a professional.

| improve this answer | |
  • When people loose data the job is always on the line - is this unusual? – Jimmy Jul 6 '11 at 20:36
  • You don't work for a lexicographer do you? – Chopper3 Jul 6 '11 at 20:38
  • That would explain the importance of a database – Jimmy Jul 6 '11 at 20:40
  • 1
    Alternatively, if it turns out the company needs more-frequent backups, you need some sort of transaction-log shipping, or clustering, or snapshotting, or other HA technology. – mfinni Jul 6 '11 at 20:52
  • 1
    True - was talking this morning with one of the directors about yesterdays glitch and he told me to meet with the financial officer to see what kind of a RPO we should be having. Not exactly his words but very similar and I believe he was fairly impressed when I mentioned RPO & RTO to him during our little meeting - which I read up on last night ;)) – Jimmy Jul 7 '11 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.