0

I had a disk failure, and I'm trying to restore my MSSQL DB. I seem to be missing the MDF file associated with the DB ( Some other older MDFs are available).

The only backup scheme is a full ntbackup dump monthly, and a daily incremental. Does ntbackup pick up the MDF files? I can't find them in my BKFs!

Am I totally screwed?

Thanks.

lrrrgg
  • 3
  • 2
  • ntbackup should be using the SQLVDI to do backups, which means that even if the SQL server data files are excluded from the backup, they should still be backed up if the SQL Service is triggered for backup. You can [read more here](http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/28/informational-shedding-light-on-vss-vdi-backups-in-sql-server.aspx) – Mark Henderson Jul 20 '11 at 23:00
  • I lucked out... VSS was on and the MDFs were in a different location ( different installation of MSSQL on same machine ). Thanks for info. – lrrrgg Jul 21 '11 at 02:08

2 Answers2

0

If the SQL Server is shutdown during the backup, you should have the MDF's. You might also have them if VSS was enabled. Otherwise, they are possibly lost - the MDF (and associated LDF) are kept open by SQL Server so ntbackup can't gain exclusive access to it.

If you have the SQL Agent performing backups to a disk-file, you may be able to locate the corresponding BAK file and perform a restore from that.

Mike Insch
  • 1,254
  • 8
  • 10
0

The MDF/LDF files are always open by MSSQL, so a straight ntbackup run won't be able to read them. You'll need to set up a maintenance plan within MSSQL to run a periodic backup of both the database and transaction logs (if you need point-in-time recovery), or enable the VSS Writer service (it's not enabled by default prior to MSSQL 2008) to enable Volume Shadow support, which would then allow ntbackup to get the MDF/LDF (but you can't do a point-in-time restore with those.)

In your case, after the fact, you're (sadly) probably out of luck.

techieb0y
  • 4,179
  • 17
  • 17