Sql-server – Restore Read Only FileGroups from File

backupenterprise-editionrestoresql serversql server 2014

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?

Best Answer

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.

Related Question