January 14, 2011 at 6:45 am
Hi,
I currently have over a 1 TB database on SQL Server 2005 Enterprise and using 30 filegroups and partitioned by month. I am looking into piecemeal restores for faster recover-ability and for the users to still view data on the filegroups that have been successfully restored. The syntax I am using is:
/* Full Backup */
backup database P_Func_Test
to disk = 'D:\FG Backup\Full_Backup1.bak',
disk = 'D:\FG Backup\Full_Backup2.bak'
with checksum, init;
/* Piecemeal Restore */
restore database P_Func_Test_R
filegroup = 'Primary'
from disk = 'D:\FG Backup\Full_Backup1.bak',
disk = 'D:\FG Backup\Full_Backup2.bak'
with move 'P_Func_Test' to 'D:\FG Backup\P_Func_Test_R.mdf',
move 'P_Func_Test_log' to 'D:\FG Backup\P_Func_Test_R_log.ldf',
partial, recovery;
restore database P_Func_Test_R
filegroup = 'Feb'
from disk = 'D:\FG Backup\Full_Backup1.bak',
disk = 'D:\FG Backup\Full_Backup2.bak'
with move 'Feb' to 'D:\FG Backup\Feb.ndf',
recovery;
restore database P_Func_Test_R
filegroup = '2011_FG6'
from disk = 'D:\FG Backup\Full_Backup1.bak',
disk = 'D:\FG Backup\Full_Backup2.bak'
with move '2011_FG6' to 'D:\FG Backup\2011_FG6.ndf',
recovery;
As you can see, all of the filegroups have not been restored. I was curious to see if I could view the data that were in those filegroups so I did a SELECT. The error message is (which I was expecting):
Msg 679, Level 16, State 1, Line 2
One of the partitions of index 'PK_test_p' for table 'dbo.test_p'(partition ID 72057594038845440) resides on a filegroup ("2006_FG1") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.
But the "Results" tab did not return any data.
Questions:
1. Can users not see the data during the piecemeal restore?
2. Would I be able to BACKUP DATABASE P_Func_Test_R FILEGROUP = '2011_FG6' instead of using a Full Backup? If so, can you please show me simple syntax so I can apply it to the database for testing?
Thank you for taking the time to read my dilemma.
January 14, 2011 at 6:57 am
Here's what BoL says about piecemeal restores.
http://msdn.microsoft.com/en-us/library/ms175541.aspx
I'm pretty sure you need to have been doing filegroup backups along the way also. No piecemeal restores from a single full backup.
January 14, 2011 at 8:02 am
Thank you for your response. By the way, I forgot to mention that the recovery model is Full.
The piecemeal restore can be done using a full backup. This is the only way I can successfully accomplish a piecemeal restore. I am having a lot of difficulty doing a piecemeal restore using filegroup backups. Could someone please help me out with some generic syntax? This is what I have been doing:
/* FileGroup Backups */
backup database P_Func_Test
filegroup = 'PRIMARY'
to disk = 'D:\FG Backup\P_Func_Test_Primary.bak'
with checksum, init;
backup database P_Func_Test
filegroup = '2011_FG6'
to disk = 'D:\FG Backup\P_Func_Test_2011.bak'
with checksum, init;
/* Piecemeal Restore Attempt - Failed */
restore database P_Func_Test_R
filegroup = 'PRIMARY'
from disk = 'D:\FG Backup\P_Func_Test_Primary.bak'
with move 'P_Func_Test' to 'D:\FG Backup\P_Func_Test_R.mdf',
move 'P_Func_Test_log' to 'D:\FG Backup\P_Func_Test_R_log.ldf',
partial, norecovery;
restore database P_Func_Test_R
filegroup = '2011_FG6'
from disk = 'D:\FG Backup\P_Func_Test_2011.bak'
with move '2011_FG6' to 'D:\FG Backup\P_Func_Test_2011.ndf',
recovery;
restore log P_Func_Test_R
from disk = 'D:\FG Backup\P_Func_Test_Log.trn'
with recovery;
I have received the following message after the second restore command:
The roll forward start point is now at log sequence number (LSN) 36000000049800001. Additional roll forward past LSN 37000000009700001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE LOG successfully processed 16 pages in 0.022 seconds (5.348 MB/sec).
---------------------------
I have found the answer to question #1. I made sure that I was accessing only the data in the restore partitions using the query:
SELECT *
FROM test
WHERE $PARTITION.PF_Partition_Function(date) = 6;
Then the query returned data:
SELECT *
FROM test
WHERE date BETWEEN '20110101' AND '20110131';
April 13, 2011 at 11:30 am
I'm having a similar issue. I'm able to query a partially restored table if I'm running queries on the partitioning key column but all the queries that are run on anyother column throw error. Here is my situation
I partitoned table1 with col1 as partitioning key.I also dropped and re-created the non-clustered index on the same partition func and scheme with same partitioning key, hence they are aligned. I restored the latest filegroup fg6 after restoring primary.
Partition Function:
CREATE PARTITION FUNCTION [part1](int) AS RANGE LEFT FOR VALUES (N'100000', N'200000', N'300000', N'400000', N’500000’)
Partition Scheme:
CREATE PARTITION SCHEME [part1] AS PARTITION [card] TO ([fg1], [fg2], [fg3], [fg4], [fg5],[fg6])
I restored filegroup fg6 with primary. And fg1,fg2,fg3,fg4 and fg5 are still in pending restore. But when I try t query the table’s data which is in fg6 I get errors for some queries. Eg:
select col1 from Table1_t where col1 >700000—works no problem
select col1 from Table1_t where col2 >700000—throws error
Above select throws an error
Msg 679, Level 16, State 1, Line 1
One of the partitions of index IX_Table1_T' for table 'dbo.table1_t'(partition ID 72057594104053760) resides on a filegroup that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.
As error’s saying some of the file groups are not online; but I know 100% for sure that all the records with col2>70000 are in fg6( which is online)
But we can make the above query work just by adding an order by clause
select col1 from Table1_t where col2 >70000 order by col1 desc--works
Similarly,
select top 1 * from table1 where col4 >'2011-04-01' --doesn't work
select top 1 * from table1 where col4 >'2011-04-01' order by col1 desc—works
My observation was that if I have the partitioning key in the where clause or order by clause then my queries run with no problem but if I have any other columns in where clause then though the index is partitioned based on the same partition function and scheme it throws error.
I'm looking for some suggestions in this regard so that I can get my application up and running by piece wise restore without having to change the application at all.
Thanks a lot for looking
May 27, 2012 at 8:00 pm
pmuddam,
Do you have any indexes on the other columns? If not, the predicates in the WHERE clause will cause SQL Server to scan the whole table.
e.g. Without an index, the predicate 'where col2 >700000' will cause SQL Server to test each the value of col2 in each row to see if the row qualifies.
Peet
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply