Restoring file group from one database to another

Question

Post reply

Restoring file group from one database to another

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
March 6, 2020 at 5:11 pm

#3731960
Hello,
I am doing some testing and looking at Filegroup articles, specifically:
https://www.sqlshack.com/database-filegroups-and-piecemeal-restores-in-sql-server/
Piecemeal restore. but I am wondering if I can apply the filegroup backup from one database to another, specifically restoring only a particular filegroup of the database, and not have to backup the entire database, and restore it the second database filegroup, if its the exact copy of the database, the purpose would be for archiving (the secondary database is for archiving). wondering how and what steps to do, I tried the following using the backup from the main database SQLShackFGDB and tried to apply the backup filegroup to the secondary database SQLShackFGDB_2, which I used this tsql statement below:
```
RESTORE DATABASE SQLShackFGDB_2
   FILE = 'archiveData',
   FILEGROUP = 'SecondarySQLShackFGDB'
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SecondarySQLShackFGDB.bak'
   WITH NORECOVERY
GO
```
then after that I get the following error message:
Msg 3154, Level 16, State 4, Line 41
The backup set holds a backup of a database other than the existing 'SQLShackFGDB_2' database.
Msg 3013, Level 16, State 1, Line 41
RESTORE DATABASE is terminating abnormally.
I tried to also include "Replace" instead of NORECOVERY and include both REPLACE,NORECOVERY, however still same error message. I also did NORECOVERY because I also wanted to apply the transaction log backup using this tsql statement below:
```
RESTORE LOG SQLShackFGDB_2
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLShackFGDBTaillog.trn'
   WITH RECOVERY
GO
```
However I haven't got that far yet, any input, advice help or even maybe another route would be great, as I would like to automate this in the future.
thanks in advance
- This topic was modified 4 years, 9 months ago by Siten0308. Reason: make more sense

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 1

Hello Everyone

just an update, I tried to do the following from this Article URL, however so far nothing works 🙁

https://www.systoolsgroup.com/how-to/fix-error-3154-in-sql-server/

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 2

Siten0308 wrote:

... but I am wondering if I can apply the filegroup backup from one database to another, specifically restoring only a particular filegroup of the database...

To the best of my knowledge and some experimentation in the past, the answer here is a solid "No". I'm not even aware of a method of restoring a read_only file group to a different database. Piecemeal restores to the original database? Sure... but not to a different database. I've not done a deep dive on it but I believe the problem is mis-matched LSNs and I'm not aware of a viable work around for such a thing.

As with all else, though, "It Depends" and I could be wrong there. We'll see what others have to say on the subject and, if there is a way others come up with, we'll both learn something new and useful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DinoRS SSCrazy Points: 2683 More actions · Answer 3

Jeff is right, the issue is around mismatched LSNs. You could try to match LSNs but I won't tell you how, because you are so deep into facing potential issues (+ it's a manual process) that it's not worth the hassle.

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 4

Hello Jeff and DinoRS, thank you for your input, sorry the secondary or the other filegroup is not read only, though knowing this now thanks for the info, just wondering if its possible still if the filegroup is not read_only?

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 5

No... you still have the LSN problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 6

Hey Everyone, thank you for all your input, so here is my question and situation of thinking of a solution.

I want to copy data from a certain filegroup, dynamically by month (or day) and copy it to archiving, then delete the data.

I assume the best thing is create a new table/filegroup, then use SSIS, do a SQL statement task, to get the data, and ETL it to the data into the new table/filegroup warehouse, then after that delete all the data from the file group and delete the file group... would that be best?

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 7

You might want to look into partitioning. If I had it to do over again (I had a similar requirement), I'd have used a Partitioned VIEW instead of a Partitioned TABLE.

If the Archive database is on the same server, there's no way I'd bother with SSIS. It can all be done in a stored procedure and triggered by a monthly job.

One of the biggest advantages of creating 1 file in 1 file group for each month is that you can set the file groups for the older months to READ_ONLY so that you no longer need to back them up or maintain their indexes, etc. Another advantage is that you don't have to do deletes... just drops.

Both Partitioned Views and Partitioned Tables have advantages and disadvantages. Partitioned Views have some extreme advantages if you need to make partial copies for lower environments (which is why I like them) but you should really read up about both. Most people make the mistake of doing trivial research and little planning for such things and this is something that you really need to plan well.

An example might be that you want the whole shebang, including the current (and empty next month, which is a MUST) to live on the Archive database so you don't need to even do a copy.

Like I said, study both methods before you even think of planning anything on this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)