How To restore/attach DB with alternate mdf file (holds indexes only)
-
SCENARIO:
SQL Server 2005 DB consists of 2 data files and 1 ldf file.
The DB Recovery Model is set to SIMPLE.
The DB was detached from SQL Server and copies of the 3 files for the DB were created on a portable USB drive and sent to a vendor for restore.
PROBLEM:
One of the 2 mdf files is unusable (corrupt I guess) and so the vendor is left with just 1 mdf file and 1 ldf file.
ADDITIONAL INFO:
The mdf file that is not usable contained the indexes for 2 tables in the DB and nothing else.
QUESTION:
Is it possible for the vendor to recover/restore/attach the database without re-doing the whole process of detaching the DB (as it is now) copying the 3 files to a USB drive and sending to the vendor again?
THOUGHTS:
I was hoping that since the corrupt/missing mdf file stored only indexes and no table data or other DB schema objects that there might be a way to still use the remaining 2 files to recover the DB so that I don’t have to re-do the whole process again and resend a completely new set of files to the vendor which can take several days to a week due to the size of the DB.
Ideas? Thoughs?
Kindest Regards,
Just say No to Facebook! -
No, this will not be possible because it isn't a backup. If you had a backup - then you could perform a partial file restore, but then again - with a backup you would either have a corrupt backup or have no reason for the partial restore.
Why are you sending the files anyways? You should be sending a backup that can be restored instead of detaching the database and sending the files.
If there is a space issue, try using any one of the available compression utilities which all come with fully feature trial editions (e.g. Quest Litespeed, Redgate SQL Backup, etc...).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
YSLGuru (10/6/2011)
I was hoping that since the corrupt/missing mdf file stored only indexes and no table data or other DB schema objectsI think everybody knows where i'm going with this 😉
Are any of the indexes clustered indexes?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle (10/8/2011)
YSLGuru (10/6/2011)
I was hoping that since the corrupt/missing mdf file stored only indexes and no table data or other DB schema objectsI think everybody knows where i'm going with this 😉
Are any of the indexes clustered indexes?
No. I don't think you can put a clustered Index on a seperate file from the table, at least not ion SQL 2005 but I'm wiling to admt I'm wrong about that if you can. These indexes are all non-clustered and they are for 2 tables only.
Kindest Regards,
Just say No to Facebook! -
Jeffrey Williams 3188 (10/6/2011)
No, this will not be possible because it isn't a backup. If you had a backup - then you could perform a partial file restore, but then again - with a backup you would either have a corrupt backup or have no reason for the partial restore.Why are you sending the files anyways? You should be sending a backup that can be restored instead of detaching the database and sending the files.
If there is a space issue, try using any one of the available compression utilities which all come with fully feature trial editions (e.g. Quest Litespeed, Redgate SQL Backup, etc...).
I appreciate your taking the time to reply, I really do. But don’t assume that because someone isn’t doing something a particular way like sending a traditional backup, that it’s because they aren’t following best practices. Our Backup & Recovery system does not allow for an option to recover to a DB backup file. My choice is to restore the files that make up the DB.
We use as our total recovery system (mail, file servers, DB’s, everything) Microsoft’s DPM (Data Protection Manager. DPM does not provide a recovery option for DB’s that produces a backup file (singular) that can be sent to someone who can then use it like a traditional SQL DB Backup. The choices with DPM for DB recovery are:
1) Restore to file the mdf & ldf files that make up the DB
2) Restore the DB to the original SQL Server instance
3) Restore the DB to any other SQL Server Instance
Any choice I make does not provide a backup file. I could restore to a new DB server and then manually do a backup but it would take a very long time and I’m trying to reduce the time and workload since we’ve already spent many man hours trying to get this vendor our DB 3 times (including sending a USB drive that was wiped clean by the time they got it). Very frustrating.
When you have a 300 GB DB it’s not easy to send it to electronically and mailing a USB drive works but is also a time consumer.
Kindest Regards,
Just say No to Facebook! -
YSLGuru (10/10/2011)
Perry Whittle (10/8/2011)
YSLGuru (10/6/2011)
I was hoping that since the corrupt/missing mdf file stored only indexes and no table data or other DB schema objectsI think everybody knows where i'm going with this 😉
Are any of the indexes clustered indexes?
No. I don't think you can put a clustered Index on a seperate file from the table, at least not ion SQL 2005 but I'm wiling to admt I'm wrong about that if you can. These indexes are all non-clustered and they are for 2 tables only.
I guess what I don't understand about this is that because the file does not contain any data, just index information then I would think you could do something like this. If I were to disable all the indexes on these 2 tables and then at a later time enabled them I would have to rebuild them to re-enable them. That means that any info they did have in them would be over written by the rebuild. The only thing that would remain the same, as far as what is stored on this supimental mdf file is the defintion or schema of the indexes. I would think then one could send a file from any copy of this same DB so long as teh index defitnions are the same.
Make sense?
Thanks
Kindest Regards,
Just say No to Facebook! -
YSLGuru (10/10/2011)
I guess what I don't understand about this is that because the file does not contain any data, just index information then I would think you could do something like this. ...Make sense?Thanks
thinking the other way you have to have all the pieces to make the database complete, as when you stopped the server to get the mdf/ndf/ldf's, they where a snapshot in a point in time...and nothing on SQL server prevents you from putting real data in an ndf, instead of just the indexes you mentioned...so if one piece is missing, i'd kind of expect it to fail validation, right?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (10/10/2011)
YSLGuru (10/10/2011)
I guess what I don't understand about this is that because the file does not contain any data, just index information then I would think you could do something like this. ...Make sense?Thanks
thinking the other way you have to have all the pieces to make the database complete, as when you stopped the server to get the mdf/ndf/ldf's, they where a snapshot in a point in time...and nothing on SQL server prevents you from putting real data in an ndf, instead of just the indexes you mentioned...so if one piece is missing, i'd kind of expect it to fail validation, right?
The missing file is an mdf file and it has only non-clustered indexes. If it were anything else but indexes I would not have even asked. My logic is that since the index(s) can be rebuilt so long as the schema of the table(s) the indexes are for has not changed (and they have not) and as long as you have the mdf file with the table data (I do) then it would seem like this would be doable. I know that if the ldf file were bad or missing the vendor could still restore the DB with a new log file. Since the mdf file missing was just for indexes it seemed like it logically should be possible to do this. I know that if I rebuild the indexes then the info they contain is re-created anyway and you have to rebuild if you disable the indexes so..
At this point it’s now a moot point since I've already started the process over again and given up on trying to send just the mdf file with the indexes on it. Last Friday I was trying to avoid doing this because t takes several days to complete due to the size of the DB and rate of the I/O used at each step. It’s also a very busy time right for us and so everything has to be done without involving the live DB. I can’t just create backup of live to a new bak file and send it.
Thanks though for taking time to chime in.
Kindest Regards,
Just say No to Facebook! -
YSLGuru (10/10/2011)
I guess what I don't understand about this is that because the file does not contain any data, just index information then I would think you could do something like this. If I were to disable all the indexes on these 2 tables and then at a later time enabled them I would have to rebuild them to re-enable them. That means that any info they did have in them would be over written by the rebuild. The only thing that would remain the same, as far as what is stored on this supimental mdf file is the defintion or schema of the indexes. I would think then one could send a file from any copy of this same DB so long as teh index defitnions are the same.Make sense?
Thanks
The only file allowed to be missing during the attach of a database is the transaction log file. With regard to the data files Books online is quite clear about this
Microsoft SQL Server Books Online
When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply