4

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.

Jimmy
  • 111
  • 6
  • 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 06 '11 at 23:25

3 Answers3

2

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.

Peter K
  • 58
  • 2
  • I have lots of mdf and ldf files inside that data folder. – Jimmy Jul 06 '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 06 '11 at 21:14
2

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

squillman
  • 37,618
  • 10
  • 90
  • 145
1

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

mfinni
  • 35,711
  • 3
  • 50
  • 86
  • When people loose data the job is always on the line - is this unusual? – Jimmy Jul 06 '11 at 20:36
  • You don't work for a lexicographer do you? – Chopper3 Jul 06 '11 at 20:38
  • That would explain the importance of a database – Jimmy Jul 06 '11 at 20:40
  • If the company was happy with nightly backups, which you have been performing, why would your job be on the line? Unless it's also your fault that the server actually died. – mfinni Jul 06 '11 at 20:51
  • 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 06 '11 at 20:52
  • Although slightly offended - I will set up more backups than the nighlty full. Cheers ! – Jimmy Jul 06 '11 at 23:27
  • Why are you offended? The business should get what they have budgeted or paid for. If they budgeted that you perform nightly backups, you're doing that. If, as it turns out, they actually need more frequent backups, they need to budget more. Read up on RPO and RTO. – mfinni Jul 07 '11 at 00:51
  • 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 07 '11 at 12:41