3

I have a database DB in simple recovery mode with filegroups PRIMARY, A, B, and C. Each filegroup has a single file, PRIMARY.MDF, A.NDF, B.NDF, and C.NDF, respectively.

First, I run:

ALTER DATABASE [DB] MODIFY FILEGROUP [A] READ_ONLY
ALTER DATABASE [DB] MODIFY FILEGROUP [B] READ_ONLY

Next, I run:

BACKUP DATABASE [DB] READ_WRITE_FILEGROUPS TO DISK = N'C:\DB.BAK'

I copy the backup file to a separate server and perform a partial restore with:

RESTORE DATABASE [DB]
FILEGROUP = 'PRIMARY', FILEGROUP = 'C'
FROM DISK = 'C:\DB.BAK'
WITH PARTIAL, RECOVERY

The database is now online on the second server, and I can query data from filegroups PRIMARY and C. The database is aware of filegroups A and B, but they are marked as offline. Is there a way to restore the remaining two read only filegroups directly from the .NDF files?

Improve this question
1
  • No, you need a backup including the data from those filegroups. The detach/attach trick won't work (SQL Server has in the file header lsn to sync logical timestamp for all files from a database, and that lsn is maintained even of the filegroup is read-only(
    – Tibor Karaszi
    Commented May 23, 2018 at 9:01

1 Answer 1

Reset to default
1

Because you backed up only the read write files of the database with the command READ_WRITE_FILEGROUPS, you will not be able to restore A or B from the backup. Ideally, you should go perform a full backup of the database and restore all of the files you want from it. This is the recommended method for many reasons

I was thinking of when or why you wouldn't do this. I'm thinking the last ditch effort you would copy and paste ndf files A or B to the location of SQL Server to try and attach with the restore (which may be out of sync and may not work anyway) is if your only backup was the one you posted about. (Only read write databases, all read only lost.) But what would be that last ditch effort?

Corrupted database? SANS blew up? Server completely offline? In all of those circumstances you're not going to be able to grab A and B anyway. Perhaps there's an automated process that copies and clones the SANS to a backup server (but why not the backup files then?) and that piece is operational while the database itself is down, then maybe I can see where you can try to grab the NDF files and attempt a reattachment, but I feel time would be better spent getting the server running and restoring those copies of the files back over.

But here is what I'd suggest as a last ditch effort if you are persistent and intent on doing this for whatever reasons. I will have a few articles at the end related to this.

If you are able to copy and paste files A and B into the folder where SQL can access them, you can attempt to do an attachment on the DB you restored. I suspect that you will need to go to the primary server to find files A and B or possibly find another source. It may be that the files are out of sync and cannot be attached. If there happened to be no activity since the backup, you may have a chance.

Here are some examples of attaching the files:

Stack Overflow on how to attach files.

Stack Overflow on users having problems attaching MDF files.

Aaron Bertrand of Sentry One suggesting to just take a full backup and restore. Bad habits of MDF files.

Improve this answer
3
  • Ugh. That was a big typo on my part... I meant to say I wanted to use the .NDF files. Let me fix the question. I'm sorry.
    – mathewb
    Commented May 23, 2018 at 1:32
  • But to follow up on this approach. Are you suggesting that I can 1) copy the .NDF files from the primary server, 2) detach the restored database from the secondary server, and 3) attach the restored database including the copied .NDF files?
    – mathewb
    Commented May 23, 2018 at 1:36
  • @mathewb I am suggesting you can try it, I feel as though the LDF will be out of sync with the NDF / MDF files and you won't be able to attach them. It is a last ditch try though. I will update my answer to match your edited question.
    – Shaulinator
    Commented May 23, 2018 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.