Single File/Filegroup Restore Failing
in [Server Configuration]
|
Prev: MSSQL2008: Configuration Documenter Tool available ?
Next: Error during pre-login handshake: SSL certificates and IIS?!
From: CqlBoy on 13 May 2010 20:18 I'm missing something here but haven't figured it out so, I could use some help. I have a test database (TestDB1) with four filegroups, each filegroup has just one file. If I take full backups of the primary and remaing filegroups and then restore as another database with different name (TestDB2), that works. Here's the problem: I insert new data in TestDB1 ... one row added to filegroup #2, for example ... and then take a diff' backup of filegroup #2 and then a log backup. Now, I have a full, diff, and log backup of filegroup #2 of which when I go to restore on TestDB2 to refresh data - just that one row, right? - the restore fails with the following message: Msg 3116, Level 16, State 1, Line 2 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 2 RESTORE DATABASE is terminating abnormally. Why won't this work? How can I make this work? I have the entire database restored (TestDB2) and only want to refresh data in one filegroup, the only filegroup where I know data has changed. Thx -CqlBoy
From: Uri Dimant on 14 May 2010 03:26 Hi The melo example taken from the BOL CREATE DATABASE mywind GO ALTER DATABASE mywind SET RECOVERY FULL ALTER DATABASE mywind ADD FILEGROUP new_customers ALTER DATABASE mywind ADD FILEGROUP sales GO ALTER DATABASE mywind ADD FILE (NAME='mywind_data_1', FILENAME='d:\mw.dat1') TO FILEGROUP new_customers ALTER DATABASE mywind ADD FILE (NAME='mywind_data_2', FILENAME='d:\mw.dat2') TO FILEGROUP sales BACKUP DATABASE mywind TO DISK ='d:\mywind.dmp' WITH INIT GO USE mywind GO CREATE TABLE mywind..t1 (id int) ON new_customers CREATE TABLE mywind..t2 (id int) ON sales GO INSERT INTO mywind..t1 (id ) VALUES (1) INSERT INTO mywind..t2 (id ) VALUES (6) GO BACKUP LOG mywind TO DISK='d:\mywind.dmp'WITH NOINIT GO DELETE FROM mywind..t2 RESTORE FILELISTONLY FROM DISK='d:\mywind.dmp' GO RESTORE HEADERONLY FROM DISK='d:\mywind.dmp' GO RESTORE DATABASE mywind_part FILEGROUP = 'sales' FROM DISK='d:\mywind.dmp' WITH FILE=1,NORECOVERY,PARTIAL, MOVE 'mywind' TO 'd:\mw2.pri', MOVE 'mywind_log' TO 'd:\mw2.log', MOVE 'mywind_data_2' TO 'd:\mw2.dat2',REPLACE GO RESTORE LOG mywind_part FROM DISK = 'd:\mywind.dmp' WITH FILE = 3,RECOVERY GO Notice that t2 is accessible after the partial restore operation. SELECT * FROM mywind_part..t2 Here is the result: --------------- 0 Notice that t1 is not accessible after the partial restore operation. SELECT COUNT(*) FROM mywind_part..t1 Here is the resulting message: The query processor is unable to produce a plan because the table 'mywind_part..t1' is marked OFFLINE. drop database mywind drop database mywind_part "CqlBoy" <CqlBoy(a)discussions.microsoft.com> wrote in message news:B4B01FF8-FDF9-4219-B402-40E723901529(a)microsoft.com... > I'm missing something here but haven't figured it out so, I could use some > help. > > I have a test database (TestDB1) with four filegroups, each filegroup has > just one file. If I take full backups of the primary and remaing > filegroups > and then restore as another database with different name (TestDB2), that > works. Here's the problem: I insert new data in TestDB1 ... one row > added > to filegroup #2, for example ... and then take a diff' backup of filegroup > #2 > and then a log backup. Now, I have a full, diff, and log backup of > filegroup > #2 of which when I go to restore on TestDB2 to refresh data - just that > one > row, right? - the restore fails with the following message: > Msg 3116, Level 16, State 1, Line 2 > 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 2 > RESTORE DATABASE is terminating abnormally. > > Why won't this work? How can I make this work? I have the entire > database > restored (TestDB2) and only want to refresh data in one filegroup, the > only > filegroup where I know data has changed. > > Thx > > -CqlBoy
From: Erland Sommarskog on 14 May 2010 03:47 CqlBoy (CqlBoy(a)discussions.microsoft.com) writes: > I have a test database (TestDB1) with four filegroups, each filegroup > has just one file. If I take full backups of the primary and remaing > filegroups and then restore as another database with different name > (TestDB2), that works. Here's the problem: I insert new data in > TestDB1 ... one row added to filegroup #2, for example ... and then take > a diff' backup of filegroup #2 and then a log backup. Now, I have a > full, diff, and log backup of filegroup #2 of which when I go to restore > on TestDB2 to refresh data - just that one row, right? - the restore > fails with the following message: > Msg 3116, Level 16, State 1, Line 2 > 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 2 > RESTORE DATABASE is terminating abnormally. > > Why won't this work? How can I make this work? I have the entire > database restored (TestDB2) and only want to refresh data in one > filegroup, the only filegroup where I know data has changed. You can't make it work. You may know that the changes made to the second filegroup does not cause any violations, but SQL Server doesn't. Say that FG2 includes a table Products, and FG1 has the table OrdersDetails. Now in the source database, you delete a product, all order details where it is referred to. If you were able to restore FG2 in the target databasee, that database would now have OrderDetails with non-existing products. Still there would be a foreign-key constraint that would be marked as trusted. That can count as nothing but corruption. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Uri Dimant on 14 May 2010 05:08
Simple like we have in 6.5 restore a single table:-) "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D78638F27513Yazorman(a)127.0.0.1... > CqlBoy (CqlBoy(a)discussions.microsoft.com) writes: >> I have a test database (TestDB1) with four filegroups, each filegroup >> has just one file. If I take full backups of the primary and remaing >> filegroups and then restore as another database with different name >> (TestDB2), that works. Here's the problem: I insert new data in >> TestDB1 ... one row added to filegroup #2, for example ... and then take >> a diff' backup of filegroup #2 and then a log backup. Now, I have a >> full, diff, and log backup of filegroup #2 of which when I go to restore >> on TestDB2 to refresh data - just that one row, right? - the restore >> fails with the following message: >> Msg 3116, Level 16, State 1, Line 2 >> 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 2 >> RESTORE DATABASE is terminating abnormally. >> >> Why won't this work? How can I make this work? I have the entire >> database restored (TestDB2) and only want to refresh data in one >> filegroup, the only filegroup where I know data has changed. > > You can't make it work. You may know that the changes made to the > second filegroup does not cause any violations, but SQL Server doesn't. > Say that FG2 includes a table Products, and FG1 has the table > OrdersDetails. > Now in the source database, you delete a product, all order details > where it is referred to. If you were able to restore FG2 in the target > databasee, that database would now have OrderDetails with non-existing > products. Still there would be a foreign-key constraint that would be > marked as trusted. That can count as nothing but corruption. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |