Backup FileGroup and Restore on Secondary Database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Backup FileGroup and Restore on Secondary...

Backup FileGroup and Restore on Secondary Database

Rate Topic

Display Mode

Topic Options

Author

Message

Razi, M

Posted Thursday, October 18, 2007 4:34 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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

Paul Randal

Posted Thursday, October 18, 2007 6:16 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Razi, M

Posted Friday, October 19, 2007 11:18 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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

Paul Randal

Posted Friday, October 19, 2007 11:25 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Razi, M

Posted Friday, October 19, 2007 2:12 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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 »

Permissions