Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup FileGroup and Restore on Secondary Database Expand / Collapse
Author
Message
Posted Thursday, October 18, 2007 4:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
Hi

I have two similar databases with identical filegroups(primary,secondary) but on different drives.
At one-point in time both the databases are in sync.
I created on a secondary filegroup and populated several thousand rows to grow the filesize

Took a backup of secondary filegroup and when I try to restore the secondary filegroup back on the secondary server I get the following error:

USE MASTER
GO
RESTORE DATABASE RaziTest -- secondary database
FILEGROUP = 'RaziTest_FileGRP2' --secondary filegroup
FROM DISK ='H:\sqldata\TempRazi\RaziTest_FileGRP2.BAK' --backup of secondary filegroup from primary server

Error:
Msg 3116, Level 16, State 2, Line 1
The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Any help in getting around this is appreciated.


Razi, M.
http://questivity.com/it-training.html
Post #412470
Posted Thursday, October 18, 2007 6:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 2,040, Visits: 1,668
It can't be done unless the databases are identical copies of each other - otherwise the LSNs in the databases get out of sync. It's an often-requested feature of SQL Server that may come in a future release.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #412508
Posted Friday, October 19, 2007 11:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
Hi,

Thanks for your response. Yes at one point in time the copies were identical. On the primary server I added few rows to a table on secondary file group, took a backup of that file group and tried to restore that backup on the secondary server.

You mean to say that LSN changes the moment we add data to the primary and that wount match on the secondary.


Razi, M.
http://questivity.com/it-training.html
Post #412936
Posted Friday, October 19, 2007 11:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 2,040, Visits: 1,668
Yup - if you take an exact copy of the database and then make a change in one that isn't reflected exactly in the other, as far as SQL Server is concerned you've got two different databases with different last-LSNs.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #412938
Posted Friday, October 19, 2007 2:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
hi,

I ran a query into sys.database_files for both the databases and LSNs match but still I didnt restore. I think that is because the recovery plans are different in both the databases (which is unusual since I restored both with the same copy).

FYI, if the secondary is in Standby then we can restore a diff backup from primary but it will be read-only but not fully recovered.

Thanks for your time. I'm investigating and will update the thread.


Razi, M.
http://questivity.com/it-training.html
Post #412995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse