September 15, 2015 at 12:22 pm
There was corruption on a box and lo and behold the only backups are of the mdf and ldf files. To add to the problem the mdf files exist on a separate secondary d: drive and the ldf files on yet another drive. If I want to attach these files once restored from tape to a different server do I also need the same drives matched up as the original? Or if I want to attach both the mdf and ldf files can I restore to only one drive with a move command?
September 15, 2015 at 12:35 pm
No, you can specify the new location of the log file after you've selected the MDF file. Use the GUI in SSMS, it's fairly easy.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 15, 2015 at 12:48 pm
Thanks, does the location of the mdf file need to be the same as the original or can it also be moved?
September 15, 2015 at 1:00 pm
No. Place the file where you want and then when attaching via the gui, just browse to it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 15, 2015 at 5:11 pm
chrisph (9/15/2015)
the only backups are of the mdf and ldf files
I hope they were "transactionally consistent" (if that is the right word?!) when they were backed up.
Suggest running DBCC CHECKDB after re-attaching for Belt & Braces test.
September 15, 2015 at 8:40 pm
Kristen-173977 (9/15/2015)
chrisph (9/15/2015)
the only backups are of the mdf and ldf filesI hope they were "transactionally consistent" (if that is the right word?!) when they were backed up.
Suggest running DBCC CHECKDB after re-attaching for Belt & Braces test.
It sounds like there was no real backup, only copies of the MDF and LDF. The encouraging news is that in order to copy the files, SQL Server has to release the lock on them. Unless SAN magic was involved to get around the locking, SQL might have been stopped to enable the copy.
chrisph, whether or not you get this database recovered, do yourself a favor and give some attention to the backups. Establish your timing for full and log backups and get them automated. Then, test them to make sure they're viable by recovering to a different database.
September 16, 2015 at 2:19 am
Ed Wagner (9/15/2015)
The encouraging news is that in order to copy the files, SQL Server has to release the lock on them.
Good point, thanks. My knowledge of tape backup software is stone-age, and its probably improved since, but the worry in my mind was the tape backup being asked to copy an "in use" file; my recollection is that what the tape backup did, when it found a file was in-use, was to add it to a list to "try again later" - e.g. at the end of the rest of the backup process. If that's the case then I suppose it is inconceivable that SQL would release the file lock on one of the MDF/LDF files and not the other, or lock one-but-not-the-other at different moments in time, so on that basis whatever is on tape should be fine. Makes me nervous though, but maybe that's totally irrational?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply