October 18, 2007 at 4:34 pm
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
October 18, 2007 at 6:16 pm
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
October 19, 2007 at 11:18 am
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
October 19, 2007 at 11:25 am
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
October 19, 2007 at 2:12 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply