Filter Expression

Hi,

I'm trying to create a query where the results are restricted by the date selected. When the user selects two different dates, the query works. However, if the user wanted to see receivals for only one day, the query returns nulls even though there are receivals for that day. Below is the code:

SELECT {{[Measures].[Tonnes]}} on 0,
NonEmptyCrossJoin({{Filter([Date].[Day].members, [Date].currentmember.name = "2006-10-16")}}, {{[Transaction].[Closing CarryOver].[IN].[Receival tonnes]}}, {{[Measures].[Tonnes]}}, 2) DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

The "2006-10-16" is the date selected by the user. Any ideas why the filter doesn't like just returned one date, would be extremely helpful! Thanks.

First, you should lose NonEmptyCrossJoin from the query, which is hurting here, and also may produce wrong results if you have more than one measure group.

The simplest query to do what you want is

SELECT {[Measures].[Tonnes]} on 0,
NON EMPTY {([Date].[Day].[2006-10-16], [Transaction].[Closing CarryOver].[IN].[Receival tonnes])} DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

Filter by months & days without years

I have an application that holds BirthDates.
Is there a way to query for all birthdates between two dates without
considering the year ?
Like: Between "April 1" and Dec 10". I know who to query between two dates,
but it considers the year.
CASystemsUse
Select *
From <TableName>
Where DateValue Between
Stuff(convert(VarChar(8), DateColumn, 112), 5, 4, '0401') And
Stuff(convert(VarChar(8), DateColumn, 112), 5, 4, '1210')
"CASystems" wrote:

> I have an application that holds BirthDates.
> Is there a way to query for all birthdates between two dates without
> considering the year ?
> Like: Between "April 1" and Dec 10". I know who to query between two dates
,
> but it considers the year.
> --
> CASystemssql

Fill label of chart X axis

Hi!

I have a report that diplay the sales per month from 2 date (start date, end date) passed in parameters. For exemple if the dates are 2006-01-01 to 2006-12-31 i want to diplay the sales for all the month between 2006-01-01 and 2006-12-31. Right now my graph display only month where sales exist. What i mean is that for this range of date my chart display only March april and may month because there are no sales for the other month. My problem is that i would like to display all month in the label on the X axix even if the month have no sales. So for this exemple i would like to diplay all month from January to december but i would have column only for the march april and may month.
From what i understand i have to use the "category group" to generate the label of my X axis but i don't know how to generate all the month from 2 date. I have to do another report that display sales per days. So i would have to generate all the day between 2 dates. How can i do such a thing ?

Thanks and sorry about my bad English ^_^

The x-axis has two modes:
* category mode (this is what you seem to be using)
* scalar mode (if "numeric / timescale data" is checked on the x-axis tab)

In the scalar mode, if you use actual DateTime objects for the category grouping, the x-axis will be drawn as a real datetime axis. So if you have sales in January and in December, it will show all the months in between in the scalar mode.

-- Robert

Fill in missing date ranges

I have a table AssetValues that contains several rows of data for a
particular date. The table has missing dates and I need to populate these
missing dates one-time to create historical data and then daily to populate
missing data on an ongoing basis. The missing dates are not related to the
particular day of the w.
The logic for inserting new rows is as follows:
If there is a date without data, insert data using the previous date that
has data as long as the previous day is in the same month. So if I was
missing data for 20060430 and there was data for 20060429 then copy the data
from 20060429 changing the date to 20060430. If I was missing 20060430 and
20060429 then use the data from 20060428.
If I was missing 20060501 do not use the data from 20060430. The data needs
to be in the same month. In this case I would need to use the data from
20060502 if this was available or the next day in May when the data is
available. If the data is not available then take no action until it is.
I have a calendar table if needed.
Thanks to anyone who could help.
CREATE TABLE AssetValues
(
AssetValueDate datetime,
Category char(1),
Subcategory int
)
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 1 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','B', 2 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','C', 3 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 4 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 5 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','C', 6 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','A', 7 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 8 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 9 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','A', 10 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','B', 11 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 12 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 13 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','B', 14 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','C', 15 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 16 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 17 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','C', 18 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','A', 19 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 20 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 21 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','A', 22 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','B', 23 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 24 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 25 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','B', 26 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','C', 27 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 28 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 29 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','C', 30 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','A', 31 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 32 )
DROP TABLE AssetValuesTry,
create view v1
as
select a.dt
from dbo.calendar as a
where not exists (
select * from dbo.AssetValues as b
where b.AssetValueDate = a.dt
)
and a.dt between convert(char(6), (select min(AssetValueDate) from
dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
'01'))
go
select *
from v1
go
-- previous available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
v1.dt) = 0)
go
-- next available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
a.AssetValueDate) = 0)
go
AMB
"Terri" wrote:

> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues
>
>|||This duplicates all rows from the previous day. Is that what you want?
CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
DECLARE @.Date DATETIME
, @.MaxDate DATETIME
SELECT @.Date = MIN(AssetValueDate)
, @.MaxDate = MAX(AssetValueDate)
FROM AssetValues
WHILE @.Date <= @.MaxDate BEGIN
INSERT Dates
SELECT @.Date
SET @.Date = @.Date + 1
END
GO
SELECT DateValue
, AssetValueDate
, Category
, Subcategory
FROM AssetValues av
, Dates
WHERE YEAR(DateValue) = YEAR(AssetValueDate)
AND
MONTH(DateValue) = MONTH(AssetValueDate)
AND
EXISTS
(
SELECT *
FROM AssetValues av1
WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
HAVING COUNT(DISTINCT AssetValueDate) = 1
)
ORDER BY DateValue, AssetValueDate
--Alan
Terri wrote:
> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues|||Thanks that worked. The only thing I still need to change is that the script
populated all the days in May from May 2nd data which was the most recent
data I had. I think I can add a date filter that will prevent population of
future dates.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87565D10-F136-4D26-988B-5208CBEED90A@.microsoft.com...
> Try,
> create view v1
> as
> select a.dt
> from dbo.calendar as a
> where not exists (
> select * from dbo.AssetValues as b
> where b.AssetValueDate = a.dt
> )
> and a.dt between convert(char(6), (select min(AssetValueDate) from
> dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
> convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
> '01'))
> go
> select *
> from v1
> go
> -- previous available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
> v1.dt) = 0)
> go
> -- next available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
> a.AssetValueDate) = 0)
> go
>
> AMB
>|||No, but thanks. Sometimes I need to populate a date with data from a more
recent date when that data becomes available.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1146684129.666739.113960@.j73g2000cwa.googlegroups.com...
> This duplicates all rows from the previous day. Is that what you want?
> CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
> DECLARE @.Date DATETIME
> , @.MaxDate DATETIME
> SELECT @.Date = MIN(AssetValueDate)
> , @.MaxDate = MAX(AssetValueDate)
> FROM AssetValues
> WHILE @.Date <= @.MaxDate BEGIN
> INSERT Dates
> SELECT @.Date
> SET @.Date = @.Date + 1
> END
> GO
> SELECT DateValue
> , AssetValueDate
> , Category
> , Subcategory
> FROM AssetValues av
> , Dates
> WHERE YEAR(DateValue) = YEAR(AssetValueDate)
> AND
> MONTH(DateValue) = MONTH(AssetValueDate)
> AND
> EXISTS
> (
> SELECT *
> FROM AssetValues av1
> WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
> HAVING COUNT(DISTINCT AssetValueDate) = 1
> )
> ORDER BY DateValue, AssetValueDate
> --Alan
> Terri wrote:
these
populate
the
that
data
and
needs
>

filegroup restore problem

Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore. But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have to
keep all the log files need for recovery ..so in fact we don’t have advantage
of putting filegroup as readonly. So any suggestions on how to avoid applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default...;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup
Consider differential backups,, perhaps on a weekly basis. This way, you
restore the filegroup, then the most-recent differential, then the remaining
logs.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore.
But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have
to
keep all the log files need for recovery ..so in fact we don’t have
advantage
of putting filegroup as readonly. So any suggestions on how to avoid
applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default...;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup
|||In addition to Tom's post:
What you are asking for is a planned feature for SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> Hi,
> We are planning to implement filegroup backup strategy for one of our big
> database. We are planning to divide the database by dates so that jan data
> will be in 1 filegroup and feb data in separate filegroup so basically we
> will have 12 filegroups per year. As the month finish we will put the
> filegroup as read only and take the filegroup backup and then later on if we
> need to recover this filegroup in case of disaster we just need to restore
> this filegroup backup and don't need to apply all the log files after the
> filegroup as this is read only and sql server should assume that since this
> is read only it should not expect log files after this filegroup restore. But
> this is not happening: when I restore the filegroup backup sql server still
> force me to apply all the log files after that. But this will mean we have to
> keep all the log files need for recovery ..so in fact we don't have advantage
> of putting filegroup as readonly. So any suggestions on how to avoid applying
> log files or we are looking for feedback about how other people are doing
> this?
> Thanks
> --Harvinder
> Note: Already reviewed this article
> http://support.microsoft.com/default...;EN-US;Q295371
> Following are the steps I am testing this:
> 1) complete/full database backup
> 2) create Jan filegroup
> 3) populate data into Jan as well as primary filegroup
> 4) transaction log backup
> 5) put Jan as Read only
> 6) Jan filegroup backup
> 7) create Feb filegroup
> 8) populate data into Feb as well as primary filegroup
> 9) transaction log backup
> 10) put Feb as Read only
> 11) Feb filegroup backup
> 12) create Mar filegroup
> 13) populate data into Mar as well as primary filegroup
> 14) transaction log backup
> 15) put Mar as Read only
> 16) Mar filegroup backup
> 17) Create Apr filegroup
> 18) populate data into Apr as well as primary filegroup
> 19) If at this point we lost Datafile belonging to Feb filegroup I expect
> only to apply backup taken at step 11) but SQL Server forced me to take the
> log backup of tail and apply backups taken at step 11, 14, t-log tail backup
> i.e. all the transaction log backups after filegroup backup
>
>
|||Tibor,
You mentioned that this will be new feature in sql server 2005. I don't see
any white paper on microsoft web site regarding backup on sql server 2005.If
you get this message and if u have any information on this topic do let me
know
Thanks
--Harvinder
"Tibor Karaszi" wrote:

> In addition to Tom's post:
> What you are asking for is a planned feature for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
> news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
>
>

filegroup restore problem

Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore. Bu
t
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have t
o
keep all the log files need for recovery ..so in fact we don’t have advant
age
of putting filegroup as readonly. So any suggestions on how to avoid applyin
g
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/defaul...b;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backupConsider differential backups,, perhaps on a weekly basis. This way, you
restore the filegroup, then the most-recent differential, then the remaining
logs.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore.
But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have
to
keep all the log files need for recovery ..so in fact we don’t have
advantage
of putting filegroup as readonly. So any suggestions on how to avoid
applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/defaul...b;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup|||In addition to Tom's post:
What you are asking for is a planned feature for SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> Hi,
> We are planning to implement filegroup backup strategy for one of our big
> database. We are planning to divide the database by dates so that jan data
> will be in 1 filegroup and feb data in separate filegroup so basically we
> will have 12 filegroups per year. As the month finish we will put the
> filegroup as read only and take the filegroup backup and then later on if
we
> need to recover this filegroup in case of disaster we just need to restore
> this filegroup backup and don't need to apply all the log files after the
> filegroup as this is read only and sql server should assume that since thi
s
> is read only it should not expect log files after this filegroup restore.
But
> this is not happening: when I restore the filegroup backup sql server stil
l
> force me to apply all the log files after that. But this will mean we have
to
> keep all the log files need for recovery ..so in fact we don't have advant
age
> of putting filegroup as readonly. So any suggestions on how to avoid apply
ing
> log files or we are looking for feedback about how other people are doing
> this?
> Thanks
> --Harvinder
> Note: Already reviewed this article
> http://support.microsoft.com/defaul...b;EN-US;Q295371
> Following are the steps I am testing this:
> 1) complete/full database backup
> 2) create Jan filegroup
> 3) populate data into Jan as well as primary filegroup
> 4) transaction log backup
> 5) put Jan as Read only
> 6) Jan filegroup backup
> 7) create Feb filegroup
> 8) populate data into Feb as well as primary filegroup
> 9) transaction log backup
> 10) put Feb as Read only
> 11) Feb filegroup backup
> 12) create Mar filegroup
> 13) populate data into Mar as well as primary filegroup
> 14) transaction log backup
> 15) put Mar as Read only
> 16) Mar filegroup backup
> 17) Create Apr filegroup
> 18) populate data into Apr as well as primary filegroup
> 19) If at this point we lost Datafile belonging to Feb filegroup I expec
t
> only to apply backup taken at step 11) but SQL Server forced me to take th
e
> log backup of tail and apply backups taken at step 11, 14, t-log tail back
up
> i.e. all the transaction log backups after filegroup backup
>
>|||Tibor,
You mentioned that this will be new feature in sql server 2005. I don't see
any white paper on microsoft web site regarding backup on sql server 2005.If
you get this message and if u have any information on this topic do let me
know
Thanks
--Harvinder
"Tibor Karaszi" wrote:

> In addition to Tom's post:
> What you are asking for is a planned feature for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
> news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
>
>

filegroup restore problem

Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and donâ't need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore. But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have to
keep all the log files need for recovery ..so in fact we donâ't have advantage
of putting filegroup as readonly. So any suggestions on how to avoid applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backupConsider differential backups,, perhaps on a weekly basis. This way, you
restore the filegroup, then the most-recent differential, then the remaining
logs.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and donâ't need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore.
But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have
to
keep all the log files need for recovery ..so in fact we donâ't have
advantage
of putting filegroup as readonly. So any suggestions on how to avoid
applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup|||In addition to Tom's post:
What you are asking for is a planned feature for SQL Server 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> Hi,
> We are planning to implement filegroup backup strategy for one of our big
> database. We are planning to divide the database by dates so that jan data
> will be in 1 filegroup and feb data in separate filegroup so basically we
> will have 12 filegroups per year. As the month finish we will put the
> filegroup as read only and take the filegroup backup and then later on if we
> need to recover this filegroup in case of disaster we just need to restore
> this filegroup backup and don't need to apply all the log files after the
> filegroup as this is read only and sql server should assume that since this
> is read only it should not expect log files after this filegroup restore. But
> this is not happening: when I restore the filegroup backup sql server still
> force me to apply all the log files after that. But this will mean we have to
> keep all the log files need for recovery ..so in fact we don't have advantage
> of putting filegroup as readonly. So any suggestions on how to avoid applying
> log files or we are looking for feedback about how other people are doing
> this?
> Thanks
> --Harvinder
> Note: Already reviewed this article
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
> Following are the steps I am testing this:
> 1) complete/full database backup
> 2) create Jan filegroup
> 3) populate data into Jan as well as primary filegroup
> 4) transaction log backup
> 5) put Jan as Read only
> 6) Jan filegroup backup
> 7) create Feb filegroup
> 8) populate data into Feb as well as primary filegroup
> 9) transaction log backup
> 10) put Feb as Read only
> 11) Feb filegroup backup
> 12) create Mar filegroup
> 13) populate data into Mar as well as primary filegroup
> 14) transaction log backup
> 15) put Mar as Read only
> 16) Mar filegroup backup
> 17) Create Apr filegroup
> 18) populate data into Apr as well as primary filegroup
> 19) If at this point we lost Datafile belonging to Feb filegroup I expect
> only to apply backup taken at step 11) but SQL Server forced me to take the
> log backup of tail and apply backups taken at step 11, 14, t-log tail backup
> i.e. all the transaction log backups after filegroup backup
>
>|||Tibor,
You mentioned that this will be new feature in sql server 2005. I don't see
any white paper on microsoft web site regarding backup on sql server 2005.If
you get this message and if u have any information on this topic do let me
know
Thanks
--Harvinder
"Tibor Karaszi" wrote:
> In addition to Tom's post:
> What you are asking for is a planned feature for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
> news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> > Hi,
> >
> > We are planning to implement filegroup backup strategy for one of our big
> > database. We are planning to divide the database by dates so that jan data
> > will be in 1 filegroup and feb data in separate filegroup so basically we
> > will have 12 filegroups per year. As the month finish we will put the
> > filegroup as read only and take the filegroup backup and then later on if we
> > need to recover this filegroup in case of disaster we just need to restore
> > this filegroup backup and don't need to apply all the log files after the
> > filegroup as this is read only and sql server should assume that since this
> > is read only it should not expect log files after this filegroup restore. But
> > this is not happening: when I restore the filegroup backup sql server still
> > force me to apply all the log files after that. But this will mean we have to
> > keep all the log files need for recovery ..so in fact we don't have advantage
> > of putting filegroup as readonly. So any suggestions on how to avoid applying
> > log files or we are looking for feedback about how other people are doing
> > this?
> >
> > Thanks
> > --Harvinder
> > Note: Already reviewed this article
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
> > Following are the steps I am testing this:
> > 1) complete/full database backup
> > 2) create Jan filegroup
> > 3) populate data into Jan as well as primary filegroup
> > 4) transaction log backup
> > 5) put Jan as Read only
> > 6) Jan filegroup backup
> > 7) create Feb filegroup
> > 8) populate data into Feb as well as primary filegroup
> > 9) transaction log backup
> > 10) put Feb as Read only
> > 11) Feb filegroup backup
> > 12) create Mar filegroup
> > 13) populate data into Mar as well as primary filegroup
> > 14) transaction log backup
> > 15) put Mar as Read only
> > 16) Mar filegroup backup
> > 17) Create Apr filegroup
> > 18) populate data into Apr as well as primary filegroup
> > 19) If at this point we lost Datafile belonging to Feb filegroup I expect
> > only to apply backup taken at step 11) but SQL Server forced me to take the
> > log backup of tail and apply backups taken at step 11, 14, t-log tail backup
> > i.e. all the transaction log backups after filegroup backup
> >
> >
> >
>
>