Filegroup Backup / Restore | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Filegroup Backup / Restore

We have a DB with 200 GB size has 11 Filegroups. All changes will be made Only to the last filegroup (FG11). We have the log shipping setup for the standby server every 15 mins. We refresh the development server every week with a copy from the production server. We would like to restore only the last filegroup instead of the whole database. I followed the following steps for filegroup backup and restore. On Production Server: BACKUP DATABASE DBTEST
FILE = ‘FG11’,
FILEGROUP = ‘FG11’
TO disk = ‘C:pROD_DB_FG11.bak’
GO On Development Server: restore database DBTEST
FILE = ‘FG11’,
FILEGROUP = ‘FG11’
FROM disk = ‘C:pROD_DB_FG11.bak’
WITH replace
GO
After restoring the last filegroup on development server, I see that it is still in the middle of restore (loading mode). I do not want to backup the transaction log from the production server because it will affect log shipping. Is there a way to bring the database to recovery mode without applying log files ? Thanks in advance,
Gopal
Did you try DBCC CHECKPOINT just before FG11 Backup?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Just run RESTORE LOG dbname WITH RECOVERY …
this will enable the db access. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I have tried
restore log DBname with recovery I get the following message: Server: Msg 4331, Level 16, State 1, Line 1
The database cannot be recovered because the files have been restored to inconsistent points in time.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally. I haven’t tried DBCC Checkpoint yet. I am not sure, whether this will have any effect on log shipping. Thanks,
Gopal
Do you have log shipping enabled on Development server?
Try using RESTORE DATABASE dbname WITH RECOVERY this should work. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks for your reply. Log shipping is not enabled on Development server. I have issued the command
restore database Dbname with recovery I get the following error message:
Server: Msg 4331, Level 16, State 1, Line 1
The database cannot be recovered because the files have been restored to inconsistent points in time.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. Is it always required to apply the Tlog after restoring the Filegroups ?
ITs weird and not required to restore log after filegroup restore. Try restoring filegroup with RECOVERY, REPLACE clause as you performed above.
BOL examples refers: RESTORE DATABASE MyNwind
FILE = ‘MyNwind_data_1’,
FILEGROUP = ‘new_customers’,
FILE = ‘MyNwind_data_2’,
FILEGROUP = ‘first_qtr_sales’
FROM MyNwind_1
WITH RECOVERY, (use here RECOVERY clause)
REPLACE _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I tried restoring the database using recovery and replace option. But it gives the following message. One or more of the options (recovery) are not supported for this statement. Review the documentation for supported options. I do agree it is wierd that we need to apply TLog for restoring filegroups. Thanks !
It worked for me without any issues, and unable to understand what is the issue at other end.
Have you tried to restore Tlog to the existing restored backup? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>