Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SQL Server Administration (2005)  issue:cannot query after piecemeal restore

Author  Topic  norulz Starting Member 1 Post Posted - 2011-04-13 : 13:02:51 I have a table that I partitioned into 6 partitions with each partition residing on its own filegroup. The table is partitioned based on a identity column that is a clustered index as maximum queries run on this column. Though we are supposed to maintain last 12 months worth data online; our application always hits the latest data. And I also wanted to implement sliding window approach hence I aligned index with data. Our growth rate of table will be 50GB per month. And on an average we will be archiving 50 million records every month. Doubts: · I made sure that first partition of table reside on first file group and second partition of table reside on second file group and so on. I implemented this because I know my application only hits latest data so in case of any disaster if I had to restore from a full backup then just restoring primary followed by the latest file group (which has latest data) should get my application up and running. And I tried this piecewise restore on a test DB. My test DB has a table called Table1. Table 1 has 1million records in it. Column Name Column type Col1(primary key) Int (identity) Col2 Int Col3 Nvarchar(50) Col4 Datetime I partitioned this table into 6 partitions where filegroup1 has 0-100K, fg2 has 100K-200K, fg3 200K-300K, fg4 300K-400K, fg5 400K-500K and fg6 500K and above. I partitioned Table 1 based on an identity column (Col_1) which is also primary key and clustered index. 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]) Partitioning Table1: ALTER TABLE [dbo].[Table1_T] ADD CONSTRAINT [PK_table1_T] PRIMARY KEY CLUSTERED ( [Col1] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part1] ([Col1]) Droping and re-creating index on same partition function: CREATE NONCLUSTERED INDEX [IX_Table11_T] ON [dbo].[Table1_T] ( [Col1] DESC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part1] ([Col1]) CREATE NONCLUSTERED INDEX [IX_Table12_T] ON [dbo].[Table1_T] ( [Col1] DESC, [Col2] DESC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part1] ([Col1]) CREATE NONCLUSTERED INDEX [IX_Table13_T] ON [dbo].[Table1_T] ( [Col3] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part1] ([Col1]) 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_Table13_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 ton.