1: yes.
2: depends;) The query execution is still driven by the optimizer. It wont do paralellization for small result sets.
3: Ah - what would that be good given that you are still reading one backup file ;)?
4: Not running the files on one underlying SAN and thus improoving your IO budget?
The most brutal thing I have ever seen like that had nearly 30 files all on separate SAN volumes (count of hard discs going close to 200) and it was done as every LUN had a queue limit in the driver of 255 outstanding requests, which the SAN (with a 32 gigabyte cache) was just not caring about ;) THat thing was pulling in nearly 1.5 gigabyte per second over multiple fiber connections.
No, it it's not possible to restore a database from an ldf file. The ldf file would be restored along with the mdf files.
No, it's not redundant as they have two different purposes.
It's important to take full backups, and transaction log backups. Only having a copy of the ldf file doesn't help you restore the database.
As to what a ldf file is for, the ldf is the transaction log. Think of it as a circular buffer that records changes to your database. When you update a row, the change is immediately written to the ldf. At some point in the future (usually less than five minutes), the modified data is written to the mdf file.
If the server crashed or there was a power failure, when SQL starts, it reads the ldf and re-applies (REDO) those changes.
Additionally, if you have a transaction that hasn't been commited and the sever crashes, all changes made by that transaction have to be undone to make the database consistent. The ldf file has that task as well. (UNDO)
I mentioned above that the ldf file is circular. Taking a transaction log backup (.trn) copies out a portion of the ldf file. After a trn file is safely created, sql can reuse that portion of the ldf file. The series of trn backups create a chain that together record every modification made to the database. Of course, if you never took a tranaction log backup, the ldf file would grow and grow and grow.
In a disaster scenario, restoring the full backup gets you a copy of the database as of the time the full backup finished. You can then restore the trn files in order and bring the database current to any point in time including up to the last trn backup.
I'm glossing over some important details, but the gist is the that ldf is a working file that records recent changes to the database. The trn files are copies of parts of the ldf made under the assumption that you will keep then safe so that sql can reuse the space in the ldf and if disaster strikes, you'll have them in an alternate location.
Best Answer
(answering my own question)
OK, so we ran a simulation - what will happen if we lose the secondary filegroup file?
Not good.
The database gets stuck in recovery mode and you can't do anything about it - you can't set the "emergency" mode, you can't change it to "single_user" mode - nothing. Basically, every command you run throws an error
Unable to open the physical file "xxx.mdf". Operating system error 2: "2 (The system cannot find the file specified.)
The only solution is to restore the database. But "rebuilding" a secondary filegroup won't work, even if there's only a fulltext index in it.