Filter expression for searching "with all the words"

Using VWD I have created a search feature using the LIKE clause. The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox. Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option). The current filter expression is:

Description LIKE '%{0}%'

For example, if “John Smith” is typed into the search textbox, the results will include a row with:

1. “John Smith is my neighbor”

but NOT a row with

2. “John is my neighbor. His last name is Smith”.

How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order? In the example above, BOTH Descriptions would be returned in the search results when “John Smith” in typed into the search textbox.

Thanks for any help you can provide in helping me refine my search options.

Sounds like you're looking for a "full-text search".

read this:http://msdn2.microsoft.com/en-us/library/ms142571.aspx

|||

To search several words,like "John Smith" ,you have to split that into "john" and "smith " then use

Description LIKE '%John%' andDescription LIKE '%Smith%'

but that operation is quite expensive,I recommend you to use fulltext search. You can see the following like for details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp

|||

Thanks for the suggestions. I will explore these options.

I do have a couple of general questions about doing searches:

1. Am I correct in assuming that when you use LIKE as I have done (Description LIKE '%{0}%') that the search results that are returned are for the EXACT word or phrase that was searched (i.e., comparable in accuracy - if not speed - to the Google exact phrase search)?

2. Since full-text searches rely on an index they do not necessarily return EXACT results because "noise" words are eliminated? Thus the best that can be hoped for is an exact word order, but not an exact phrase?

3. Can the combined filter expression cited above (Description LIKE '%John%' and Description LIKE '%Smith%) contain parameters instead of the hardcoded search terms (Description LIKE '%{0}%' andDescription LIKE '%{0}%) that allow any combination of terms to be entered into the search textbox?

I look forward to researching the full-text approach and getting it implemented into my search page. Thanks again.

sql

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 does not work on a group?

I must be missing something. I have a table with two groups. One groups on
the user who created the record the other on the date the record was created.
I sort on the username group by name and the createdate group with a
category type.
My dataset pulls information for the current year however in this table I
want to only display data for the last week. If I apply the filter to the
table I receive expected results however if I apply the filter to the group
nothing comes back.
For either group if I put "= #2/22/2005#" for the expression "<=" as the
operator and "= Fields!CreateDate" as the Value I return 0 records despite
having some. I know my syntax is correct becaus this works at the table
level.
Can anyone tell me what I am missing?And you have date values in there more recent than 2/22/2005?
2/22/2005 <= Fields!CreateDate
I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
>I must be missing something. I have a table with two groups. One groups
>on
> the user who created the record the other on the date the record was
> created.
> I sort on the username group by name and the createdate group with a
> category type.
> My dataset pulls information for the current year however in this table I
> want to only display data for the last week. If I apply the filter to the
> table I receive expected results however if I apply the filter to the
> group
> nothing comes back.
> For either group if I put "= #2/22/2005#" for the expression "<=" as the
> operator and "= Fields!CreateDate" as the Value I return 0 records despite
> having some. I know my syntax is correct becaus this works at the table
> level.
> Can anyone tell me what I am missing?|||Same result. I know there are values past 2/22/2005 because I see them
without the filter applied. The filter works at the table level just not the
group level. This is strange.
"Jeff A. Stucker" wrote:
> And you have date values in there more recent than 2/22/2005?
> 2/22/2005 <= Fields!CreateDate
> I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
> the value.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
> news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
> >I must be missing something. I have a table with two groups. One groups
> >on
> > the user who created the record the other on the date the record was
> > created.
> > I sort on the username group by name and the createdate group with a
> > category type.
> >
> > My dataset pulls information for the current year however in this table I
> > want to only display data for the last week. If I apply the filter to the
> > table I receive expected results however if I apply the filter to the
> > group
> > nothing comes back.
> >
> > For either group if I put "= #2/22/2005#" for the expression "<=" as the
> > operator and "= Fields!CreateDate" as the Value I return 0 records despite
> > having some. I know my syntax is correct becaus this works at the table
> > level.
> >
> > Can anyone tell me what I am missing?
>
>

Filter by parameter value

Is this possible? I searched and couldnt find a solution that would
allow me to filter my results by a paramter value that I want the user
to be able to enter. My filtering works fine when entering in a numeric
value in manually but not when running it by the parameter. I get the
following error message: 'Fail to Evaluate
FilterExpression/FilterValues'Most likely the data types of the filter expression and the filter value
expression don't match.
Please try something similar to this:
Filter expression:
=CInt(Fields!SomeField.Value)
Filter value expression:
=CInt(Parameters!SomeParameter.Value)
The important part is the CInt() function call which will ensure that you
are comparing values of identical data types. More information on data type
conversion functions can be found on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent" <Brent.Raymond@.gmail.com> wrote in message
news:1126284534.290617.315760@.g14g2000cwa.googlegroups.com...
> Is this possible? I searched and couldnt find a solution that would
> allow me to filter my results by a paramter value that I want the user
> to be able to enter. My filtering works fine when entering in a numeric
> value in manually but not when running it by the parameter. I get the
> following error message: 'Fail to Evaluate
> FilterExpression/FilterValues'
>

Filter by a concatenated column?

I am using a query in a stored procedure, where the user can dictate which
field they want to sort by. That I can do. What I also want to do is
filter out the null values for the selected field, and one of the fields is
a concatenated result. This is the query:
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
so the stored procedure would be something like this:
CREATE PROCEDURE [dbo].ClientSearch]
@.SearchField as varchar(50)='' AS
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
where @.SearchField is not null order by @.SearchField
If the user is selecting FullName, I get an error saying the @.SearchField is
an invalid column name -- however this works if other field names are
selected, and it also works if I only want to order by FullName, but not
filter.
Thanks for your help.I hope you are using exec since its a dyamic query.
fullname is a concatenationo of two fields LName + ', ' + FName
therefore LName + ', ' + FName is not null does not work.
make sure that LName is not null and FNAME is not NULL
HTH
Rajesh Peddireddy.
"news.microsoft.com" wrote:

> I am using a query in a stored procedure, where the user can dictate which
> field they want to sort by. That I can do. What I also want to do is
> filter out the null values for the selected field, and one of the fields i
s
> a concatenated result. This is the query:
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> so the stored procedure would be something like this:
> CREATE PROCEDURE [dbo].ClientSearch]
> @.SearchField as varchar(50)='' AS
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> where @.SearchField is not null order by @.SearchField
> If the user is selecting FullName, I get an error saying the @.SearchField
is
> an invalid column name -- however this works if other field names are
> selected, and it also works if I only want to order by FullName, but not
> filter.
> Thanks for your help.
>
>

Filter by "all" or actual value issue

I have a string parameter bound to a dataset that contains a "UNION Select
'(All)' " statement to provide the user a way to return all values instead of
just a single value.
I have other integer parameters that use this exact syntax for filtering
(ALL) and they work fine. The syntax looks like this and it doesn't work and
I have no idea why:
Expression: =Iif( Parameters!PropCode.Label = "All", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
Operation: = Value: =True
Maybe it has to do with the "(" character? Maybe it has to do with it being
a string? Please advise how I might get past this issue.Did you try:
Iif( Parameters!PropCode.Label = "(All)", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
'|||Yes. That was a typo. That is actually what the filter looks like. This
doesn't work.
"Jihong.Liu" wrote:
> Did you try:
> Iif( Parameters!PropCode.Label = "(All)", True,
> Fields!PropertyCode.Value = Parameters!PropCode.Label)
> '
>sql

Filter and parameter

I am using SQL Server reporting service against Oracle DB. Is there any way
I can make filters optional? I want that if user does not select any value
from filter dropdown list then the query should run only with query
parameters. In my report I have 3 query parameters and 1 filter. Filter is
using expression (=Iif(Parameters!Equip1.Value is Nothing, true,
Fields!AC_AIRCRAFT_ID.Value=Parameters!Equip1.Value)) to use one of the
report parameters(not query parameter). Now when I deploy the report, I am
forced to provide value for the filter. Please note that I have allowed null
and blank value for the report parameter I am using for this filter.
--
ThanksOpen the report in report designer and add a default value for the report
parameter Equip1, e.g. =Nothing
Before republishing to the server, you have to delete the existing report
from the server. Then you are no longer forced to provide an explicit value.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:DB936A62-60E9-4B66-9A3F-D2D2308B6A36@.microsoft.com...
>I am using SQL Server reporting service against Oracle DB. Is there any
>way
> I can make filters optional? I want that if user does not select any value
> from filter dropdown list then the query should run only with query
> parameters. In my report I have 3 query parameters and 1 filter. Filter is
> using expression (=Iif(Parameters!Equip1.Value is Nothing, true,
> Fields!AC_AIRCRAFT_ID.Value=Parameters!Equip1.Value)) to use one of the
> report parameters(not query parameter). Now when I deploy the report, I am
> forced to provide value for the filter. Please note that I have allowed
> null
> and blank value for the report parameter I am using for this filter.
> --
> Thanks

FileUpload control and SQL Server 2005.

Hello,

I have a FileUpload control on my webform. I would like a user to be able to upload a file to the server. Is there any way to store this file in SQL Server 2005?

Thanks

ASPSQL66

Here is a piece of code from one of my projects.

DataTable reqTable =new DataTable ("Request");SqlDataAdapter dataAdapter =new SqlDataAdapter ("Select * FROM Request", sqlconn);DataRow dataCurrentRow =null;SqlCommandBuilder builder =new SqlCommandBuilder (dataAdapter);builder.QuotePrefix ="[";builder.QuoteSuffix ="]";dataAdapter.Fill (reqTable);foreach (DataRow dataRowin reqTable.Rows){if ((long) dataRow["ID"] == requestID){dataCurrentRow = dataRow;break;}}dataCurrentRow["Attachment"] = fuAttachment.FileBytes;dataAdapter.Update (reqTable);

The above code updates the [Attachment] VARBINARY(MAX) field of the [Request] table where theID of the row matchesrequestID variable's value.fuAttachment is the FileUpload control on the page. I ripped off the exception handling and other error checking code.

Hope this helps

Fileshare subscription

Hello
I have a problem where I set up a user subscription, putting in all the
required fields for this to run correctly. I schedule it to run Once. I
check in SQL Agent for the job and it exists, as expected. I check that the
job has been run in SQL Agent, which is says it has done, successfully
(succeeded with datetime). I then look at the Subscriptions tab (in Report
Manager) to see whether it has run, though it still displays "New
Subscription" for "Last Run Status". The file also does not exist within the
directory specified. I have also looked at the Application Log in Event
Viewer (on the sever), though there isn't anything to indicate a problem
with the subscription.
Is there anywhere I can see what the problem may be?
Thanks,
MarkLook in the log from Reporting Service Server.
Could be that this is a permission issue.
If you have any questions further post the last lines of the log
here.
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"MCC" <cmc_za@.hotmail.com> schrieb im Newsbeitrag
news:e1ySHlNQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hello
> I have a problem where I set up a user subscription, putting in all the
> required fields for this to run correctly. I schedule it to run Once. I
> check in SQL Agent for the job and it exists, as expected. I check that
> the
> job has been run in SQL Agent, which is says it has done, successfully
> (succeeded with datetime). I then look at the Subscriptions tab (in Report
> Manager) to see whether it has run, though it still displays "New
> Subscription" for "Last Run Status". The file also does not exist within
> the
> directory specified. I have also looked at the Application Log in Event
> Viewer (on the sever), though there isn't anything to indicate a problem
> with the subscription.
> Is there anywhere I can see what the problem may be?
> Thanks,
> Mark
>|||Thanks.
From the "ReportServer__04_14_2005_00_02_16.log" file:
aspnet_wp!library!1590!04/14/2005-12:17:59:: i INFO: Call to
GetPermissions:/
aspnet_wp!library!1590!04/14/2005-12:17:59:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!ec8!04/14/2005-12:18:05:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!1590!04/14/2005-12:18:08:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!1590!04/14/2005-12:18:08:: i INFO: Call to
GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
Entries Posted
aspnet_wp!library!684!04/14/2005-12:18:29:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!684!04/14/2005-12:18:29:: i INFO: Call to
GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
Entries Posted
aspnet_wp!library!ec8!04/14/2005-12:18:35:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!ec8!04/14/2005-12:18:35:: i INFO: Call to
GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
Entries Posted
aspnet_wp!library!684!04/14/2005-12:18:42:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!684!04/14/2005-12:18:42:: i INFO: Call to
GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
Entries Posted
aspnet_wp!library!ec8!04/14/2005-12:18:43:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!1590!04/14/2005-12:21:39:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!4fd98!4/14/2005-12:22:16:: i INFO: Cleaned 0 batch
records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0
running jobs
Thanks,
Mark
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O8kgBGOQFHA.896@.TK2MSFTNGP10.phx.gbl...
> Look in the log from Reporting Service Server.
> Could be that this is a permission issue.
> If you have any questions further post the last lines of the log
> here.
> HTH, Jens Süßmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "MCC" <cmc_za@.hotmail.com> schrieb im Newsbeitrag
> news:e1ySHlNQFHA.244@.TK2MSFTNGP12.phx.gbl...
> > Hello
> >
> > I have a problem where I set up a user subscription, putting in all the
> > required fields for this to run correctly. I schedule it to run Once. I
> > check in SQL Agent for the job and it exists, as expected. I check that
> > the
> > job has been run in SQL Agent, which is says it has done, successfully
> > (succeeded with datetime). I then look at the Subscriptions tab (in
Report
> > Manager) to see whether it has run, though it still displays "New
> > Subscription" for "Last Run Status". The file also does not exist within
> > the
> > directory specified. I have also looked at the Application Log in Event
> > Viewer (on the sever), though there isn't anything to indicate a problem
> > with the subscription.
> >
> > Is there anywhere I can see what the problem may be?
> >
> > Thanks,
> >
> > Mark
> >
> >
>|||Looks like an incomplete stack trace... of an exception block,
í don´t exactly know who is gonna pump out the file to the share, the
ReportServerService Account or the ASP.NET process the IIS is running on (I
guess the ASp.NET Account). Give him the appopiate permissions to access to
fileshare and you gotta be perhaps right.
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"MCC" <cmc_za@.hotmail.com> schrieb im Newsbeitrag
news:Op0SOSOQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Thanks.
> From the "ReportServer__04_14_2005_00_02_16.log" file:
> aspnet_wp!library!1590!04/14/2005-12:17:59:: i INFO: Call to
> GetPermissions:/
> aspnet_wp!library!1590!04/14/2005-12:17:59:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!ec8!04/14/2005-12:18:05:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!1590!04/14/2005-12:18:08:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!1590!04/14/2005-12:18:08:: i INFO: Call to
> GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> Entries Posted
> aspnet_wp!library!684!04/14/2005-12:18:29:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!684!04/14/2005-12:18:29:: i INFO: Call to
> GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> Entries Posted
> aspnet_wp!library!ec8!04/14/2005-12:18:35:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!ec8!04/14/2005-12:18:35:: i INFO: Call to
> GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> Entries Posted
> aspnet_wp!library!684!04/14/2005-12:18:42:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!684!04/14/2005-12:18:42:: i INFO: Call to
> GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> Entries Posted
> aspnet_wp!library!ec8!04/14/2005-12:18:43:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!1590!04/14/2005-12:21:39:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!4fd98!4/14/2005-12:22:16:: i INFO: Cleaned 0 batch
> records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0
> running jobs
> Thanks,
> Mark
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:O8kgBGOQFHA.896@.TK2MSFTNGP10.phx.gbl...
>> Look in the log from Reporting Service Server.
>> Could be that this is a permission issue.
>> If you have any questions further post the last lines of the log
>> here.
>> HTH, Jens Süßmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>>
>> "MCC" <cmc_za@.hotmail.com> schrieb im Newsbeitrag
>> news:e1ySHlNQFHA.244@.TK2MSFTNGP12.phx.gbl...
>> > Hello
>> >
>> > I have a problem where I set up a user subscription, putting in all the
>> > required fields for this to run correctly. I schedule it to run Once. I
>> > check in SQL Agent for the job and it exists, as expected. I check that
>> > the
>> > job has been run in SQL Agent, which is says it has done, successfully
>> > (succeeded with datetime). I then look at the Subscriptions tab (in
> Report
>> > Manager) to see whether it has run, though it still displays "New
>> > Subscription" for "Last Run Status". The file also does not exist
>> > within
>> > the
>> > directory specified. I have also looked at the Application Log in Event
>> > Viewer (on the sever), though there isn't anything to indicate a
>> > problem
>> > with the subscription.
>> >
>> > Is there anywhere I can see what the problem may be?
>> >
>> > Thanks,
>> >
>> > Mark
>> >
>> >
>>
>|||Hi Jens
Thanks for your reply, it pointed me in the right direction : ReportServer
service was, in fact, using the Local System account and not domain\svcSQL.
I also looked at the posting, below, for further information and loosely
followed the Solution #2 and now my fileshare is working fine. Now onto why
e-mails aren't... :-)
http://groups.google.co.uk/groups?q=%22My+problem+was+solved+by+calling%22&hl=en&lr=&safe=off&rls=GGLD,GGLD:2004-37,GGLD:en&selm=ODE70opMEHA.268%40TK2MSFTNGP11.phx.gbl&rnum=1
Thanks,
Mark
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eWCSFYOQFHA.3384@.TK2MSFTNGP10.phx.gbl...
> Looks like an incomplete stack trace... of an exception block,
> í don´t exactly know who is gonna pump out the file to the share, the
> ReportServerService Account or the ASP.NET process the IIS is running on
(I
> guess the ASp.NET Account). Give him the appopiate permissions to access
to
> fileshare and you gotta be perhaps right.
> HTH, Jens Süßmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "MCC" <cmc_za@.hotmail.com> schrieb im Newsbeitrag
> news:Op0SOSOQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> > Thanks.
> >
> > From the "ReportServer__04_14_2005_00_02_16.log" file:
> >
> > aspnet_wp!library!1590!04/14/2005-12:17:59:: i INFO: Call to
> > GetPermissions:/
> > aspnet_wp!library!1590!04/14/2005-12:17:59:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!ec8!04/14/2005-12:18:05:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!1590!04/14/2005-12:18:08:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!1590!04/14/2005-12:18:08:: i INFO: Call to
> > GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> > Entries Posted
> > aspnet_wp!library!684!04/14/2005-12:18:29:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!684!04/14/2005-12:18:29:: i INFO: Call to
> > GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> > Entries Posted
> > aspnet_wp!library!ec8!04/14/2005-12:18:35:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!ec8!04/14/2005-12:18:35:: i INFO: Call to
> > GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> > Entries Posted
> > aspnet_wp!library!684!04/14/2005-12:18:42:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!684!04/14/2005-12:18:42:: i INFO: Call to
> > GetPermissions:/Customer Reports/Reports/Subscription Testing/Premium
> > Entries Posted
> > aspnet_wp!library!ec8!04/14/2005-12:18:43:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!1590!04/14/2005-12:21:39:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!4fd98!4/14/2005-12:22:16:: i INFO: Cleaned 0 batch
> > records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks,
0
> > running jobs
> >
> > Thanks,
> >
> > Mark
> >
> >
> >
> > "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> > in
> > message news:O8kgBGOQFHA.896@.TK2MSFTNGP10.phx.gbl...
> >> Look in the log from Reporting Service Server.
> >> Could be that this is a permission issue.
> >>
> >> If you have any questions further post the last lines of the log
> >> here.
> >>
> >> HTH, Jens Süßmeyer.
> >>
> >> --
> >> http://www.sqlserver2005.de
> >> --
> >>
> >>
> >> "MCC" <cmc_za@.hotmail.com> schrieb im Newsbeitrag
> >> news:e1ySHlNQFHA.244@.TK2MSFTNGP12.phx.gbl...
> >> > Hello
> >> >
> >> > I have a problem where I set up a user subscription, putting in all
the
> >> > required fields for this to run correctly. I schedule it to run Once.
I
> >> > check in SQL Agent for the job and it exists, as expected. I check
that
> >> > the
> >> > job has been run in SQL Agent, which is says it has done,
successfully
> >> > (succeeded with datetime). I then look at the Subscriptions tab (in
> > Report
> >> > Manager) to see whether it has run, though it still displays "New
> >> > Subscription" for "Last Run Status". The file also does not exist
> >> > within
> >> > the
> >> > directory specified. I have also looked at the Application Log in
Event
> >> > Viewer (on the sever), though there isn't anything to indicate a
> >> > problem
> >> > with the subscription.
> >> >
> >> > Is there anywhere I can see what the problem may be?
> >> >
> >> > Thanks,
> >> >
> >> > Mark
> >> >
> >> >
> >>
> >>
> >
> >
>

Filegroup restore

Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3 tables
(T1,T2,T3) created on each of the filegroup respectively.
Do I need to perform a full database backup before I start performing
individual FG backups ?
If not, and say I backup FG1 and do not have backups for FG2 and FG3, Can I
restore the database with just FG1 and have atleast the table T1 tied to it
? or do I need to restore all the FGs to make the database active again ?
I know SQL 2005 has something where we can restore just the Primary FG and
the database can be up again .. Just dont know about SQL 2000.
Any help here would be much appreciated . ThanksHi
The partial DB online is a new feature in SQL Server 2005.
From BOL for SQL Server 2000:
"Use BACKUP to back up database files and filegroups instead of the full
database when time constraints make a full database backup impractical. To
back up a file instead of the full database, put procedures in place to
ensure that all files in the database are backed up regularly. Also,
separate transaction log backups must be performed. After restoring a file
backup, apply the transaction log to roll the file contents forward to make
it consistent with the rest of the database"
The key point is that if you do a restore, you need all the transaction
logs from the time the file group backup was made, up to the other most
current file group's transaction. An the Db need to be put into a loading
state, so you can not restore whilst users are using the DB.
Even on our very big DB's, we don't use filegroup backups as the chances for
a problem occurring a re so much bigger as each transaction log needs to be
fully accounted for.
If space is an issue for you, look at full Backup, Transaction log and
Incremental Backup cycle as an alternative, but more manageable solution.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3
tables
> (T1,T2,T3) created on each of the filegroup respectively.
> Do I need to perform a full database backup before I start performing
> individual FG backups ?
> If not, and say I backup FG1 and do not have backups for FG2 and FG3, Can
I
> restore the database with just FG1 and have atleast the table T1 tied to
it
> ? or do I need to restore all the FGs to make the database active again ?
> I know SQL 2005 has something where we can restore just the Primary FG and
> the database can be up again .. Just dont know about SQL 2000.
> Any help here would be much appreciated . Thanks
>|||Well its the backups we were looking at but most important being able to
scale . Looking at multiple tables on different FGs and then using a
partitioned view.. And that being.. historical data would stay in a
filegroup that would never change.. So all data before this year would be in
some FGs that would never be updated and could be in read only state. So
backing those once a month may suffice. So this is all in thinking stage
right now :) and hence wanted to know what to do when say a server crashes
and I may not have the latest FG backup i,e of this year.. but does that
mean I can restore all the previous years FGs that I may have and have the
database up and running ?
Thats where Im a bit confused on what I need to restore and would it work
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> Hi
> The partial DB online is a new feature in SQL Server 2005.
> From BOL for SQL Server 2000:
> "Use BACKUP to back up database files and filegroups instead of the full
> database when time constraints make a full database backup impractical. To
> back up a file instead of the full database, put procedures in place to
> ensure that all files in the database are backed up regularly. Also,
> separate transaction log backups must be performed. After restoring a file
> backup, apply the transaction log to roll the file contents forward to
make
> it consistent with the rest of the database"
> The key point is that if you do a restore, you need all the transaction
> logs from the time the file group backup was made, up to the other most
> current file group's transaction. An the Db need to be put into a loading
> state, so you can not restore whilst users are using the DB.
> Even on our very big DB's, we don't use filegroup backups as the chances
for
> a problem occurring a re so much bigger as each transaction log needs to
be
> fully accounted for.
> If space is an issue for you, look at full Backup, Transaction log and
> Incremental Backup cycle as an alternative, but more manageable solution.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3
> tables
> > (T1,T2,T3) created on each of the filegroup respectively.
> >
> > Do I need to perform a full database backup before I start performing
> > individual FG backups ?
> > If not, and say I backup FG1 and do not have backups for FG2 and FG3,
Can
> I
> > restore the database with just FG1 and have atleast the table T1 tied to
> it
> > ? or do I need to restore all the FGs to make the database active again
?
> >
> > I know SQL 2005 has something where we can restore just the Primary FG
and
> > the database can be up again .. Just dont know about SQL 2000.
> >
> > Any help here would be much appreciated . Thanks
> >
> >
>|||Hi
Currently with SQL Server 2000, if you have a FG backup done 1 June 2004 and
have a failure today. You need that FG backup, plus all transaction logs
since then (~10 months of log dumps). Not a feasible solution for you.
The exact same rule applies to SQL Server 2005. You need to transaction logs
as SQL Server can not assume that nothing has been done to those pages in
that filegroup since the backup was taken.
Currently, having the data in a separate DB, presented as a View would be
your answer.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> Well its the backups we were looking at but most important being able to
> scale . Looking at multiple tables on different FGs and then using a
> partitioned view.. And that being.. historical data would stay in a
> filegroup that would never change.. So all data before this year would be
in
> some FGs that would never be updated and could be in read only state. So
> backing those once a month may suffice. So this is all in thinking stage
> right now :) and hence wanted to know what to do when say a server crashes
> and I may not have the latest FG backup i,e of this year.. but does that
> mean I can restore all the previous years FGs that I may have and have the
> database up and running ?
> Thats where Im a bit confused on what I need to restore and would it work
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> > Hi
> >
> > The partial DB online is a new feature in SQL Server 2005.
> >
> > From BOL for SQL Server 2000:
> > "Use BACKUP to back up database files and filegroups instead of the full
> > database when time constraints make a full database backup impractical.
To
> > back up a file instead of the full database, put procedures in place to
> > ensure that all files in the database are backed up regularly. Also,
> > separate transaction log backups must be performed. After restoring a
file
> > backup, apply the transaction log to roll the file contents forward to
> make
> > it consistent with the rest of the database"
> >
> > The key point is that if you do a restore, you need all the transaction
> > logs from the time the file group backup was made, up to the other most
> > current file group's transaction. An the Db need to be put into a
loading
> > state, so you can not restore whilst users are using the DB.
> >
> > Even on our very big DB's, we don't use filegroup backups as the chances
> for
> > a problem occurring a re so much bigger as each transaction log needs to
> be
> > fully accounted for.
> >
> > If space is an issue for you, look at full Backup, Transaction log and
> > Incremental Backup cycle as an alternative, but more manageable
solution.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3
> > tables
> > > (T1,T2,T3) created on each of the filegroup respectively.
> > >
> > > Do I need to perform a full database backup before I start performing
> > > individual FG backups ?
> > > If not, and say I backup FG1 and do not have backups for FG2 and FG3,
> Can
> > I
> > > restore the database with just FG1 and have atleast the table T1 tied
to
> > it
> > > ? or do I need to restore all the FGs to make the database active
again
> ?
> > >
> > > I know SQL 2005 has something where we can restore just the Primary FG
> and
> > > the database can be up again .. Just dont know about SQL 2000.
> > >
> > > Any help here would be much appreciated . Thanks
> > >
> > >
> >
> >
>|||do i need the Tlogs to recover the db or just get it to point in time ? I am
not worried about getting it to the point in time .. If i restore the June
2004 FG, can I recover the database and have data up until June 2004 ?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> Currently with SQL Server 2000, if you have a FG backup done 1 June 2004
and
> have a failure today. You need that FG backup, plus all transaction logs
> since then (~10 months of log dumps). Not a feasible solution for you.
> The exact same rule applies to SQL Server 2005. You need to transaction
logs
> as SQL Server can not assume that nothing has been done to those pages in
> that filegroup since the backup was taken.
> Currently, having the data in a separate DB, presented as a View would be
> your answer.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> > Well its the backups we were looking at but most important being able to
> > scale . Looking at multiple tables on different FGs and then using a
> > partitioned view.. And that being.. historical data would stay in a
> > filegroup that would never change.. So all data before this year would
be
> in
> > some FGs that would never be updated and could be in read only state. So
> > backing those once a month may suffice. So this is all in thinking stage
> > right now :) and hence wanted to know what to do when say a server
crashes
> > and I may not have the latest FG backup i,e of this year.. but does that
> > mean I can restore all the previous years FGs that I may have and have
the
> > database up and running ?
> >
> > Thats where Im a bit confused on what I need to restore and would it
work
> >
> > Thanks
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> > > Hi
> > >
> > > The partial DB online is a new feature in SQL Server 2005.
> > >
> > > From BOL for SQL Server 2000:
> > > "Use BACKUP to back up database files and filegroups instead of the
full
> > > database when time constraints make a full database backup
impractical.
> To
> > > back up a file instead of the full database, put procedures in place
to
> > > ensure that all files in the database are backed up regularly. Also,
> > > separate transaction log backups must be performed. After restoring a
> file
> > > backup, apply the transaction log to roll the file contents forward to
> > make
> > > it consistent with the rest of the database"
> > >
> > > The key point is that if you do a restore, you need all the
transaction
> > > logs from the time the file group backup was made, up to the other
most
> > > current file group's transaction. An the Db need to be put into a
> loading
> > > state, so you can not restore whilst users are using the DB.
> > >
> > > Even on our very big DB's, we don't use filegroup backups as the
chances
> > for
> > > a problem occurring a re so much bigger as each transaction log needs
to
> > be
> > > fully accounted for.
> > >
> > > If space is an issue for you, look at full Backup, Transaction log and
> > > Incremental Backup cycle as an alternative, but more manageable
> solution.
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > > > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have
3
> > > tables
> > > > (T1,T2,T3) created on each of the filegroup respectively.
> > > >
> > > > Do I need to perform a full database backup before I start
performing
> > > > individual FG backups ?
> > > > If not, and say I backup FG1 and do not have backups for FG2 and
FG3,
> > Can
> > > I
> > > > restore the database with just FG1 and have atleast the table T1
tied
> to
> > > it
> > > > ? or do I need to restore all the FGs to make the database active
> again
> > ?
> > > >
> > > > I know SQL 2005 has something where we can restore just the Primary
FG
> > and
> > > > the database can be up again .. Just dont know about SQL 2000.
> > > >
> > > > Any help here would be much appreciated . Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi
Yes, but then no other FG can be later than June 2004.
Try this on your test machine and then document it as it becomes very
difficult to figure out things in a DR scenario.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I
am
> not worried about getting it to the point in time .. If i restore the
June
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi
> >
> > Currently with SQL Server 2000, if you have a FG backup done 1 June 2004
> and
> > have a failure today. You need that FG backup, plus all transaction logs
> > since then (~10 months of log dumps). Not a feasible solution for you.
> >
> > The exact same rule applies to SQL Server 2005. You need to transaction
> logs
> > as SQL Server can not assume that nothing has been done to those pages
in
> > that filegroup since the backup was taken.
> >
> > Currently, having the data in a separate DB, presented as a View would
be
> > your answer.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> > > Well its the backups we were looking at but most important being able
to
> > > scale . Looking at multiple tables on different FGs and then using a
> > > partitioned view.. And that being.. historical data would stay in a
> > > filegroup that would never change.. So all data before this year would
> be
> > in
> > > some FGs that would never be updated and could be in read only state.
So
> > > backing those once a month may suffice. So this is all in thinking
stage
> > > right now :) and hence wanted to know what to do when say a server
> crashes
> > > and I may not have the latest FG backup i,e of this year.. but does
that
> > > mean I can restore all the previous years FGs that I may have and have
> the
> > > database up and running ?
> > >
> > > Thats where Im a bit confused on what I need to restore and would it
> work
> > >
> > > Thanks
> > >
> > > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > > news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> > > > Hi
> > > >
> > > > The partial DB online is a new feature in SQL Server 2005.
> > > >
> > > > From BOL for SQL Server 2000:
> > > > "Use BACKUP to back up database files and filegroups instead of the
> full
> > > > database when time constraints make a full database backup
> impractical.
> > To
> > > > back up a file instead of the full database, put procedures in place
> to
> > > > ensure that all files in the database are backed up regularly. Also,
> > > > separate transaction log backups must be performed. After restoring
a
> > file
> > > > backup, apply the transaction log to roll the file contents forward
to
> > > make
> > > > it consistent with the rest of the database"
> > > >
> > > > The key point is that if you do a restore, you need all the
> transaction
> > > > logs from the time the file group backup was made, up to the other
> most
> > > > current file group's transaction. An the Db need to be put into a
> > loading
> > > > state, so you can not restore whilst users are using the DB.
> > > >
> > > > Even on our very big DB's, we don't use filegroup backups as the
> chances
> > > for
> > > > a problem occurring a re so much bigger as each transaction log
needs
> to
> > > be
> > > > fully accounted for.
> > > >
> > > > If space is an issue for you, look at full Backup, Transaction log
and
> > > > Incremental Backup cycle as an alternative, but more manageable
> > solution.
> > > >
> > > > Regards
> > > > --
> > > > Mike Epprecht, Microsoft SQL Server MVP
> > > > Zurich, Switzerland
> > > >
> > > > IM: mike@.epprecht.net
> > > >
> > > > MVP Program: http://www.microsoft.com/mvp
> > > >
> > > > Blog: http://www.msmvps.com/epprecht/
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > > > > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and
have
> 3
> > > > tables
> > > > > (T1,T2,T3) created on each of the filegroup respectively.
> > > > >
> > > > > Do I need to perform a full database backup before I start
> performing
> > > > > individual FG backups ?
> > > > > If not, and say I backup FG1 and do not have backups for FG2 and
> FG3,
> > > Can
> > > > I
> > > > > restore the database with just FG1 and have atleast the table T1
> tied
> > to
> > > > it
> > > > > ? or do I need to restore all the FGs to make the database active
> > again
> > > ?
> > > > >
> > > > > I know SQL 2005 has something where we can restore just the
Primary
> FG
> > > and
> > > > > the database can be up again .. Just dont know about SQL 2000.
> > > > >
> > > > > Any help here would be much appreciated . Thanks
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hassan
>Do I need to perform a full database backup before I >start performing
>individual FG backups ?
Yes , you have to do FULL BACKUP DATABASE and as Mike mentioned to perform
T-LOG BACKUP as well
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
insert test..test default values
insert test..test_GR default values
SELECT * FROM test..test_GR
SELECT * FROM test..test
BACKUP DATABASE test
TO disk='D:\Test_backup.bak'with init
BACKUP DATABASE test
FILE = 'ww',
FILEGROUP = 'ww_Group'
TO disk='D:\CROUPFILES.bak'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf' WITH NOINIT
GO
TRUNCATE TABLE test..test_GR
GO
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH NORECOVERY
RESTORE DATABASE test
FILE = 'ww',
FILEGROUP = 'ww_Group'
FROM DISK ='D:\CROUPFILES.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
GO
DROP DATABASE test
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I
am
> not worried about getting it to the point in time .. If i restore the
June
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi
> >
> > Currently with SQL Server 2000, if you have a FG backup done 1 June 2004
> and
> > have a failure today. You need that FG backup, plus all transaction logs
> > since then (~10 months of log dumps). Not a feasible solution for you.
> >
> > The exact same rule applies to SQL Server 2005. You need to transaction
> logs
> > as SQL Server can not assume that nothing has been done to those pages
in
> > that filegroup since the backup was taken.
> >
> > Currently, having the data in a separate DB, presented as a View would
be
> > your answer.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> > > Well its the backups we were looking at but most important being able
to
> > > scale . Looking at multiple tables on different FGs and then using a
> > > partitioned view.. And that being.. historical data would stay in a
> > > filegroup that would never change.. So all data before this year would
> be
> > in
> > > some FGs that would never be updated and could be in read only state.
So
> > > backing those once a month may suffice. So this is all in thinking
stage
> > > right now :) and hence wanted to know what to do when say a server
> crashes
> > > and I may not have the latest FG backup i,e of this year.. but does
that
> > > mean I can restore all the previous years FGs that I may have and have
> the
> > > database up and running ?
> > >
> > > Thats where Im a bit confused on what I need to restore and would it
> work
> > >
> > > Thanks
> > >
> > > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > > news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> > > > Hi
> > > >
> > > > The partial DB online is a new feature in SQL Server 2005.
> > > >
> > > > From BOL for SQL Server 2000:
> > > > "Use BACKUP to back up database files and filegroups instead of the
> full
> > > > database when time constraints make a full database backup
> impractical.
> > To
> > > > back up a file instead of the full database, put procedures in place
> to
> > > > ensure that all files in the database are backed up regularly. Also,
> > > > separate transaction log backups must be performed. After restoring
a
> > file
> > > > backup, apply the transaction log to roll the file contents forward
to
> > > make
> > > > it consistent with the rest of the database"
> > > >
> > > > The key point is that if you do a restore, you need all the
> transaction
> > > > logs from the time the file group backup was made, up to the other
> most
> > > > current file group's transaction. An the Db need to be put into a
> > loading
> > > > state, so you can not restore whilst users are using the DB.
> > > >
> > > > Even on our very big DB's, we don't use filegroup backups as the
> chances
> > > for
> > > > a problem occurring a re so much bigger as each transaction log
needs
> to
> > > be
> > > > fully accounted for.
> > > >
> > > > If space is an issue for you, look at full Backup, Transaction log
and
> > > > Incremental Backup cycle as an alternative, but more manageable
> > solution.
> > > >
> > > > Regards
> > > > --
> > > > Mike Epprecht, Microsoft SQL Server MVP
> > > > Zurich, Switzerland
> > > >
> > > > IM: mike@.epprecht.net
> > > >
> > > > MVP Program: http://www.microsoft.com/mvp
> > > >
> > > > Blog: http://www.msmvps.com/epprecht/
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > > > > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and
have
> 3
> > > > tables
> > > > > (T1,T2,T3) created on each of the filegroup respectively.
> > > > >
> > > > > Do I need to perform a full database backup before I start
> performing
> > > > > individual FG backups ?
> > > > > If not, and say I backup FG1 and do not have backups for FG2 and
> FG3,
> > > Can
> > > > I
> > > > > restore the database with just FG1 and have atleast the table T1
> tied
> > to
> > > > it
> > > > > ? or do I need to restore all the FGs to make the database active
> > again
> > > ?
> > > > >
> > > > > I know SQL 2005 has something where we can restore just the
Primary
> FG
> > > and
> > > > > the database can be up again .. Just dont know about SQL 2000.
> > > > >
> > > > > Any help here would be much appreciated . Thanks
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||In addition to the other posts: In SQL Server 2005, you will not need to apply the tlog backups if
the file group has been read only since the backup you restored.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I am
> not worried about getting it to the point in time .. If i restore the June
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> Currently with SQL Server 2000, if you have a FG backup done 1 June 2004
> and
>> have a failure today. You need that FG backup, plus all transaction logs
>> since then (~10 months of log dumps). Not a feasible solution for you.
>> The exact same rule applies to SQL Server 2005. You need to transaction
> logs
>> as SQL Server can not assume that nothing has been done to those pages in
>> that filegroup since the backup was taken.
>> Currently, having the data in a separate DB, presented as a View would be
>> your answer.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
>> > Well its the backups we were looking at but most important being able to
>> > scale . Looking at multiple tables on different FGs and then using a
>> > partitioned view.. And that being.. historical data would stay in a
>> > filegroup that would never change.. So all data before this year would
> be
>> in
>> > some FGs that would never be updated and could be in read only state. So
>> > backing those once a month may suffice. So this is all in thinking stage
>> > right now :) and hence wanted to know what to do when say a server
> crashes
>> > and I may not have the latest FG backup i,e of this year.. but does that
>> > mean I can restore all the previous years FGs that I may have and have
> the
>> > database up and running ?
>> >
>> > Thats where Im a bit confused on what I need to restore and would it
> work
>> >
>> > Thanks
>> >
>> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> > news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
>> > > Hi
>> > >
>> > > The partial DB online is a new feature in SQL Server 2005.
>> > >
>> > > From BOL for SQL Server 2000:
>> > > "Use BACKUP to back up database files and filegroups instead of the
> full
>> > > database when time constraints make a full database backup
> impractical.
>> To
>> > > back up a file instead of the full database, put procedures in place
> to
>> > > ensure that all files in the database are backed up regularly. Also,
>> > > separate transaction log backups must be performed. After restoring a
>> file
>> > > backup, apply the transaction log to roll the file contents forward to
>> > make
>> > > it consistent with the rest of the database"
>> > >
>> > > The key point is that if you do a restore, you need all the
> transaction
>> > > logs from the time the file group backup was made, up to the other
> most
>> > > current file group's transaction. An the Db need to be put into a
>> loading
>> > > state, so you can not restore whilst users are using the DB.
>> > >
>> > > Even on our very big DB's, we don't use filegroup backups as the
> chances
>> > for
>> > > a problem occurring a re so much bigger as each transaction log needs
> to
>> > be
>> > > fully accounted for.
>> > >
>> > > If space is an issue for you, look at full Backup, Transaction log and
>> > > Incremental Backup cycle as an alternative, but more manageable
>> solution.
>> > >
>> > > Regards
>> > > --
>> > > Mike Epprecht, Microsoft SQL Server MVP
>> > > Zurich, Switzerland
>> > >
>> > > IM: mike@.epprecht.net
>> > >
>> > > MVP Program: http://www.microsoft.com/mvp
>> > >
>> > > Blog: http://www.msmvps.com/epprecht/
>> > >
>> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> > > news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
>> > > > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have
> 3
>> > > tables
>> > > > (T1,T2,T3) created on each of the filegroup respectively.
>> > > >
>> > > > Do I need to perform a full database backup before I start
> performing
>> > > > individual FG backups ?
>> > > > If not, and say I backup FG1 and do not have backups for FG2 and
> FG3,
>> > Can
>> > > I
>> > > > restore the database with just FG1 and have atleast the table T1
> tied
>> to
>> > > it
>> > > > ? or do I need to restore all the FGs to make the database active
>> again
>> > ?
>> > > >
>> > > > I know SQL 2005 has something where we can restore just the Primary
> FG
>> > and
>> > > > the database can be up again .. Just dont know about SQL 2000.
>> > > >
>> > > > Any help here would be much appreciated . Thanks
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>|||Hi Uri
Thanks for such a good example. I played around your example and landed on
one more question.
After the backup of database and transaction log stage I add more changes to
the Primary group table by running "insert test..test default values". I
backed up the primary file group files and transaction log files and I ran
following steps
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH NORECOVERY
RESTORE DATABASE test
FILE = 'test',
FILEGROUP = 'primary'
FROM DISK ='D:\CROUPFILES1.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
I do not see new changes made after the full backup
Why so'
Regards
Mangesh
All other steps being the same I loose changes made after backup in the
primary file.
Does it mean that you can use FG (filegroup backup ) as a means of driving
element in the database recovery. I though
"Uri Dimant" wrote:
> Hassan
> >Do I need to perform a full database backup before I >start performing
> >individual FG backups ?
> Yes , you have to do FULL BACKUP DATABASE and as Mike mentioned to perform
> T-LOG BACKUP as well
> CREATE DATABASE test
> GO
> ALTER DATABASE test SET RECOVERY FULL
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
>
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
> insert test..test default values
> insert test..test_GR default values
> SELECT * FROM test..test_GR
> SELECT * FROM test..test
>
> BACKUP DATABASE test
> TO disk='D:\Test_backup.bak'with init
> BACKUP DATABASE test
> FILE = 'ww',
> FILEGROUP = 'ww_Group'
> TO disk='D:\CROUPFILES.bak'WITH INIT
> BACKUP LOG test
> TO disk='D:\Test__log.ldf'WITH INIT
> BACKUP LOG test
> TO disk='D:\Test__log.ldf' WITH NOINIT
> GO
> TRUNCATE TABLE test..test_GR
> GO
>
> RESTORE DATABASE test
> from disk='D:\Test_backup.bak'WITH NORECOVERY
>
> RESTORE DATABASE test
> FILE = 'ww',
> FILEGROUP = 'ww_Group'
> FROM DISK ='D:\CROUPFILES.bak'
> WITH FILE = 1,NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 1, NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 2, RECOVERY
> GO
> DROP DATABASE test
>
>
>
>
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> > do i need the Tlogs to recover the db or just get it to point in time ? I
> am
> > not worried about getting it to the point in time .. If i restore the
> June
> > 2004 FG, can I recover the database and have data up until June 2004 ?
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > Hi
> > >
> > > Currently with SQL Server 2000, if you have a FG backup done 1 June 2004
> > and
> > > have a failure today. You need that FG backup, plus all transaction logs
> > > since then (~10 months of log dumps). Not a feasible solution for you.
> > >
> > > The exact same rule applies to SQL Server 2005. You need to transaction
> > logs
> > > as SQL Server can not assume that nothing has been done to those pages
> in
> > > that filegroup since the backup was taken.
> > >
> > > Currently, having the data in a separate DB, presented as a View would
> be
> > > your answer.
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> > > > Well its the backups we were looking at but most important being able
> to
> > > > scale . Looking at multiple tables on different FGs and then using a
> > > > partitioned view.. And that being.. historical data would stay in a
> > > > filegroup that would never change.. So all data before this year would
> > be
> > > in
> > > > some FGs that would never be updated and could be in read only state.
> So
> > > > backing those once a month may suffice. So this is all in thinking
> stage
> > > > right now :) and hence wanted to know what to do when say a server
> > crashes
> > > > and I may not have the latest FG backup i,e of this year.. but does
> that
> > > > mean I can restore all the previous years FGs that I may have and have
> > the
> > > > database up and running ?
> > > >
> > > > Thats where Im a bit confused on what I need to restore and would it
> > work
> > > >
> > > > Thanks
> > > >
> > > > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > > > news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> > > > > Hi
> > > > >
> > > > > The partial DB online is a new feature in SQL Server 2005.
> > > > >
> > > > > From BOL for SQL Server 2000:
> > > > > "Use BACKUP to back up database files and filegroups instead of the
> > full
> > > > > database when time constraints make a full database backup
> > impractical.
> > > To
> > > > > back up a file instead of the full database, put procedures in place
> > to
> > > > > ensure that all files in the database are backed up regularly. Also,
> > > > > separate transaction log backups must be performed. After restoring
> a
> > > file
> > > > > backup, apply the transaction log to roll the file contents forward
> to
> > > > make
> > > > > it consistent with the rest of the database"
> > > > >
> > > > > The key point is that if you do a restore, you need all the
> > transaction
> > > > > logs from the time the file group backup was made, up to the other
> > most
> > > > > current file group's transaction. An the Db need to be put into a
> > > loading
> > > > > state, so you can not restore whilst users are using the DB.
> > > > >
> > > > > Even on our very big DB's, we don't use filegroup backups as the
> > chances
> > > > for
> > > > > a problem occurring a re so much bigger as each transaction log
> needs
> > to
> > > > be
> > > > > fully accounted for.
> > > > >
> > > > > If space is an issue for you, look at full Backup, Transaction log
> and
> > > > > Incremental Backup cycle as an alternative, but more manageable
> > > solution.
> > > > >
> > > > > Regards
> > > > > --
> > > > > Mike Epprecht, Microsoft SQL Server MVP
> > > > > Zurich, Switzerland
> > > > >
> > > > > IM: mike@.epprecht.net
> > > > >
> > > > > MVP Program: http://www.microsoft.com/mvp
> > > > >
> > > > > Blog: http://www.msmvps.com/epprecht/
> > > > >
> > > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > > news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > > > > > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and
> have
> > 3
> > > > > tables
> > > > > > (T1,T2,T3) created on each of the filegroup respectively.
> > > > > >
> > > > > > Do I need to perform a full database backup before I start
> > performing
> > > > > > individual FG backups ?
> > > > > > If not, and say I backup FG1 and do not have backups for FG2 and
> > FG3,
> > > > Can
> > > > > I
> > > > > > restore the database with just FG1 and have atleast the table T1
> > tied
> > > to
> > > > > it
> > > > > > ? or do I need to restore all the FGs to make the database active
> > > again
> > > > ?
> > > > > >
> > > > > > I know SQL 2005 has something where we can restore just the
> Primary
> > FG
> > > > and
> > > > > > the database can be up again .. Just dont know about SQL 2000.
> > > > > >
> > > > > > Any help here would be much appreciated . Thanks
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>|||This works for me fine
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
insert test..test default values
insert test..test_GR default values
SELECT * FROM test..test_GR
SELECT * FROM test..test
BACKUP DATABASE test
TO disk='D:\Test_backup.bak'with init
BACKUP DATABASE test
FILE = 'test ',
FILEGROUP = 'primary'
TO disk='D:\CROUPFILES.bak'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf' WITH NOINIT
GO
TRUNCATE TABLE test..test_GR
GO
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH noRECOVERY
RESTORE DATABASE test
FILE = 'test',
FILEGROUP = 'primary'
FROM DISK ='D:\CROUPFILES.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
GO
DROP DATABASE test
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:4E9B24F7-8A13-43B6-BF4D-208FB115429E@.microsoft.com...
> Hi Uri
> Thanks for such a good example. I played around your example and landed
on
> one more question.
> After the backup of database and transaction log stage I add more changes
to
> the Primary group table by running "insert test..test default values". I
> backed up the primary file group files and transaction log files and I ran
> following steps
> RESTORE DATABASE test
> from disk='D:\Test_backup.bak'WITH NORECOVERY
> RESTORE DATABASE test
> FILE = 'test',
> FILEGROUP = 'primary'
> FROM DISK ='D:\CROUPFILES1.bak'
> WITH FILE = 1,NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 1, NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 2, RECOVERY
>
> I do not see new changes made after the full backup
> Why so'
> Regards
> Mangesh
>
> All other steps being the same I loose changes made after backup in the
> primary file.
> Does it mean that you can use FG (filegroup backup ) as a means of
driving
> element in the database recovery. I though
>
>
>
> "Uri Dimant" wrote:
> > Hassan
> > >Do I need to perform a full database backup before I >start performing
> > >individual FG backups ?
> >
> > Yes , you have to do FULL BACKUP DATABASE and as Mike mentioned to
perform
> > T-LOG BACKUP as well
> >
> > CREATE DATABASE test
> > GO
> > ALTER DATABASE test SET RECOVERY FULL
> > ALTER DATABASE test
> > ADD FILEGROUP ww_Group
> > GO
> > ALTER DATABASE test
> > ADD FILE
> > ( NAME = ww,
> > FILENAME = 'D:\wwdat1.ndf',
> > SIZE = 5MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 5MB)
> > TO FILEGROUP ww_Group
> >
> >
> > create table test..test(id int identity) on [primary]
> > create table test..test_GR(id int identity) on ww_Group
> >
> >
> > insert test..test default values
> > insert test..test_GR default values
> >
> > SELECT * FROM test..test_GR
> > SELECT * FROM test..test
> >
> >
> > BACKUP DATABASE test
> > TO disk='D:\Test_backup.bak'with init
> >
> > BACKUP DATABASE test
> > FILE = 'ww',
> > FILEGROUP = 'ww_Group'
> > TO disk='D:\CROUPFILES.bak'WITH INIT
> > BACKUP LOG test
> > TO disk='D:\Test__log.ldf'WITH INIT
> > BACKUP LOG test
> > TO disk='D:\Test__log.ldf' WITH NOINIT
> >
> > GO
> > TRUNCATE TABLE test..test_GR
> >
> > GO
> >
> >
> > RESTORE DATABASE test
> > from disk='D:\Test_backup.bak'WITH NORECOVERY
> >
> >
> >
> > RESTORE DATABASE test
> > FILE = 'ww',
> > FILEGROUP = 'ww_Group'
> > FROM DISK ='D:\CROUPFILES.bak'
> > WITH FILE = 1,NORECOVERY
> > RESTORE LOG test
> > FROM disk='D:\Test__log.ldf'
> > WITH FILE = 1, NORECOVERY
> > RESTORE LOG test
> > FROM disk='D:\Test__log.ldf'
> > WITH FILE = 2, RECOVERY
> > GO
> > DROP DATABASE test
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> > > do i need the Tlogs to recover the db or just get it to point in time
? I
> > am
> > > not worried about getting it to the point in time .. If i restore the
> > June
> > > 2004 FG, can I recover the database and have data up until June 2004 ?
> > >
> > > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > > news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > > Hi
> > > >
> > > > Currently with SQL Server 2000, if you have a FG backup done 1 June
2004
> > > and
> > > > have a failure today. You need that FG backup, plus all transaction
logs
> > > > since then (~10 months of log dumps). Not a feasible solution for
you.
> > > >
> > > > The exact same rule applies to SQL Server 2005. You need to
transaction
> > > logs
> > > > as SQL Server can not assume that nothing has been done to those
pages
> > in
> > > > that filegroup since the backup was taken.
> > > >
> > > > Currently, having the data in a separate DB, presented as a View
would
> > be
> > > > your answer.
> > > >
> > > > Regards
> > > > --
> > > > Mike Epprecht, Microsoft SQL Server MVP
> > > > Zurich, Switzerland
> > > >
> > > > IM: mike@.epprecht.net
> > > >
> > > > MVP Program: http://www.microsoft.com/mvp
> > > >
> > > > Blog: http://www.msmvps.com/epprecht/
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> > > > > Well its the backups we were looking at but most important being
able
> > to
> > > > > scale . Looking at multiple tables on different FGs and then using
a
> > > > > partitioned view.. And that being.. historical data would stay in
a
> > > > > filegroup that would never change.. So all data before this year
would
> > > be
> > > > in
> > > > > some FGs that would never be updated and could be in read only
state.
> > So
> > > > > backing those once a month may suffice. So this is all in thinking
> > stage
> > > > > right now :) and hence wanted to know what to do when say a server
> > > crashes
> > > > > and I may not have the latest FG backup i,e of this year.. but
does
> > that
> > > > > mean I can restore all the previous years FGs that I may have and
have
> > > the
> > > > > database up and running ?
> > > > >
> > > > > Thats where Im a bit confused on what I need to restore and would
it
> > > work
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > > > > news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> > > > > > Hi
> > > > > >
> > > > > > The partial DB online is a new feature in SQL Server 2005.
> > > > > >
> > > > > > From BOL for SQL Server 2000:
> > > > > > "Use BACKUP to back up database files and filegroups instead of
the
> > > full
> > > > > > database when time constraints make a full database backup
> > > impractical.
> > > > To
> > > > > > back up a file instead of the full database, put procedures in
place
> > > to
> > > > > > ensure that all files in the database are backed up regularly.
Also,
> > > > > > separate transaction log backups must be performed. After
restoring
> > a
> > > > file
> > > > > > backup, apply the transaction log to roll the file contents
forward
> > to
> > > > > make
> > > > > > it consistent with the rest of the database"
> > > > > >
> > > > > > The key point is that if you do a restore, you need all the
> > > transaction
> > > > > > logs from the time the file group backup was made, up to the
other
> > > most
> > > > > > current file group's transaction. An the Db need to be put into
a
> > > > loading
> > > > > > state, so you can not restore whilst users are using the DB.
> > > > > >
> > > > > > Even on our very big DB's, we don't use filegroup backups as the
> > > chances
> > > > > for
> > > > > > a problem occurring a re so much bigger as each transaction log
> > needs
> > > to
> > > > > be
> > > > > > fully accounted for.
> > > > > >
> > > > > > If space is an issue for you, look at full Backup, Transaction
log
> > and
> > > > > > Incremental Backup cycle as an alternative, but more manageable
> > > > solution.
> > > > > >
> > > > > > Regards
> > > > > > --
> > > > > > Mike Epprecht, Microsoft SQL Server MVP
> > > > > > Zurich, Switzerland
> > > > > >
> > > > > > IM: mike@.epprecht.net
> > > > > >
> > > > > > MVP Program: http://www.microsoft.com/mvp
> > > > > >
> > > > > > Blog: http://www.msmvps.com/epprecht/
> > > > > >
> > > > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > > > news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > > > > > > Say I have a database with 3 user filegroups (FG1,FG2,FG3) and
> > have
> > > 3
> > > > > > tables
> > > > > > > (T1,T2,T3) created on each of the filegroup respectively.
> > > > > > >
> > > > > > > Do I need to perform a full database backup before I start
> > > performing
> > > > > > > individual FG backups ?
> > > > > > > If not, and say I backup FG1 and do not have backups for FG2
and
> > > FG3,
> > > > > Can
> > > > > > I
> > > > > > > restore the database with just FG1 and have atleast the table
T1
> > > tied
> > > > to
> > > > > > it
> > > > > > > ? or do I need to restore all the FGs to make the database
active
> > > > again
> > > > > ?
> > > > > > >
> > > > > > > I know SQL 2005 has something where we can restore just the
> > Primary
> > > FG
> > > > > and
> > > > > > > the database can be up again .. Just dont know about SQL 2000.
> > > > > > >
> > > > > > > Any help here would be much appreciated . Thanks
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >

Filegroup restore

Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3 tables
(T1,T2,T3) created on each of the filegroup respectively.
Do I need to perform a full database backup before I start performing
individual FG backups ?
If not, and say I backup FG1 and do not have backups for FG2 and FG3, Can I
restore the database with just FG1 and have atleast the table T1 tied to it
? or do I need to restore all the FGs to make the database active again ?
I know SQL 2005 has something where we can restore just the Primary FG and
the database can be up again .. Just dont know about SQL 2000.
Any help here would be much appreciated . ThanksHi
The partial DB online is a new feature in SQL Server 2005.
From BOL for SQL Server 2000:
"Use BACKUP to back up database files and filegroups instead of the full
database when time constraints make a full database backup impractical. To
back up a file instead of the full database, put procedures in place to
ensure that all files in the database are backed up regularly. Also,
separate transaction log backups must be performed. After restoring a file
backup, apply the transaction log to roll the file contents forward to make
it consistent with the rest of the database"
The key point is that if you do a restore, you need all the transaction
logs from the time the file group backup was made, up to the other most
current file group's transaction. An the Db need to be put into a loading
state, so you can not restore whilst users are using the DB.
Even on our very big DB's, we don't use filegroup backups as the chances for
a problem occurring a re so much bigger as each transaction log needs to be
fully accounted for.
If space is an issue for you, look at full Backup, Transaction log and
Incremental Backup cycle as an alternative, but more manageable solution.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3
tables
> (T1,T2,T3) created on each of the filegroup respectively.
> Do I need to perform a full database backup before I start performing
> individual FG backups ?
> If not, and say I backup FG1 and do not have backups for FG2 and FG3, Can
I
> restore the database with just FG1 and have atleast the table T1 tied to
it
> ? or do I need to restore all the FGs to make the database active again ?
> I know SQL 2005 has something where we can restore just the Primary FG and
> the database can be up again .. Just dont know about SQL 2000.
> Any help here would be much appreciated . Thanks
>|||Well its the backups we were looking at but most important being able to
scale . Looking at multiple tables on different FGs and then using a
partitioned view.. And that being.. historical data would stay in a
filegroup that would never change.. So all data before this year would be in
some FGs that would never be updated and could be in read only state. So
backing those once a month may suffice. So this is all in thinking stage
right now and hence wanted to know what to do when say a server crashes
and I may not have the latest FG backup i,e of this year.. but does that
mean I can restore all the previous years FGs that I may have and have the
database up and running ?
Thats where Im a bit confused on what I need to restore and would it work
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> Hi
> The partial DB online is a new feature in SQL Server 2005.
> From BOL for SQL Server 2000:
> "Use BACKUP to back up database files and filegroups instead of the full
> database when time constraints make a full database backup impractical. To
> back up a file instead of the full database, put procedures in place to
> ensure that all files in the database are backed up regularly. Also,
> separate transaction log backups must be performed. After restoring a file
> backup, apply the transaction log to roll the file contents forward to
make
> it consistent with the rest of the database"
> The key point is that if you do a restore, you need all the transaction
> logs from the time the file group backup was made, up to the other most
> current file group's transaction. An the Db need to be put into a loading
> state, so you can not restore whilst users are using the DB.
> Even on our very big DB's, we don't use filegroup backups as the chances
for
> a problem occurring a re so much bigger as each transaction log needs to
be
> fully accounted for.
> If space is an issue for you, look at full Backup, Transaction log and
> Incremental Backup cycle as an alternative, but more manageable solution.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> tables
Can[vbcol=seagreen]
> I
> it
?[vbcol=seagreen]
and[vbcol=seagreen]
>|||Hi
Currently with SQL Server 2000, if you have a FG backup done 1 June 2004 and
have a failure today. You need that FG backup, plus all transaction logs
since then (~10 months of log dumps). Not a feasible solution for you.
The exact same rule applies to SQL Server 2005. You need to transaction logs
as SQL Server can not assume that nothing has been done to those pages in
that filegroup since the backup was taken.
Currently, having the data in a separate DB, presented as a View would be
your answer.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> Well its the backups we were looking at but most important being able to
> scale . Looking at multiple tables on different FGs and then using a
> partitioned view.. And that being.. historical data would stay in a
> filegroup that would never change.. So all data before this year would be
in
> some FGs that would never be updated and could be in read only state. So
> backing those once a month may suffice. So this is all in thinking stage
> right now and hence wanted to know what to do when say a server crashes
> and I may not have the latest FG backup i,e of this year.. but does that
> mean I can restore all the previous years FGs that I may have and have the
> database up and running ?
> Thats where Im a bit confused on what I need to restore and would it work
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
To[vbcol=seagreen]
file[vbcol=seagreen]
> make
loading[vbcol=seagreen]
> for
> be
solution.[vbcol=seagreen]
> Can
to[vbcol=seagreen]
again[vbcol=seagreen]
> ?
> and
>|||do i need the Tlogs to recover the db or just get it to point in time ? I am
not worried about getting it to the point in time .. If i restore the June
2004 FG, can I recover the database and have data up until June 2004 ?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> Currently with SQL Server 2000, if you have a FG backup done 1 June 2004
and
> have a failure today. You need that FG backup, plus all transaction logs
> since then (~10 months of log dumps). Not a feasible solution for you.
> The exact same rule applies to SQL Server 2005. You need to transaction
logs
> as SQL Server can not assume that nothing has been done to those pages in
> that filegroup since the backup was taken.
> Currently, having the data in a separate DB, presented as a View would be
> your answer.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
be[vbcol=seagreen]
> in
crashes[vbcol=seagreen]
the[vbcol=seagreen]
work[vbcol=seagreen]
full[vbcol=seagreen]
impractical.[vbcol=seagreen]
> To
to[vbcol=seagreen]
> file
transaction[vbcol=seagreen]
most[vbcol=seagreen]
> loading
chances[vbcol=seagreen]
to[vbcol=seagreen]
> solution.
3[vbcol=seagreen]
performing[vbcol=seagreen]
FG3,[vbcol=seagreen]
tied[vbcol=seagreen]
> to
> again
FG[vbcol=seagreen]
>|||Hi
Yes, but then no other FG can be later than June 2004.
Try this on your test machine and then document it as it becomes very
difficult to figure out things in a DR scenario.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I
am
> not worried about getting it to the point in time .. If i restore the
June
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> and
> logs
in[vbcol=seagreen]
be[vbcol=seagreen]
to[vbcol=seagreen]
> be
So[vbcol=seagreen]
stage[vbcol=seagreen]
> crashes
that[vbcol=seagreen]
> the
> work
> full
> impractical.
> to
a[vbcol=seagreen]
to[vbcol=seagreen]
> transaction
> most
> chances
needs[vbcol=seagreen]
> to
and[vbcol=seagreen]
have[vbcol=seagreen]
> 3
> performing
> FG3,
> tied
Primary[vbcol=seagreen]
> FG
>|||Hassan
>Do I need to perform a full database backup before I >start performing
>individual FG backups ?
Yes , you have to do FULL BACKUP DATABASE and as Mike mentioned to perform
T-LOG BACKUP as well
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
insert test..test default values
insert test..test_GR default values
SELECT * FROM test..test_GR
SELECT * FROM test..test
BACKUP DATABASE test
TO disk='D:\Test_backup.bak'with init
BACKUP DATABASE test
FILE = 'ww',
FILEGROUP = 'ww_Group'
TO disk='D:\CROUPFILES.bak'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf' WITH NOINIT
GO
TRUNCATE TABLE test..test_GR
GO
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH NORECOVERY
RESTORE DATABASE test
FILE = 'ww',
FILEGROUP = 'ww_Group'
FROM DISK ='D:\CROUPFILES.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
GO
DROP DATABASE test
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I
am
> not worried about getting it to the point in time .. If i restore the
June
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> and
> logs
in[vbcol=seagreen]
be[vbcol=seagreen]
to[vbcol=seagreen]
> be
So[vbcol=seagreen]
stage[vbcol=seagreen]
> crashes
that[vbcol=seagreen]
> the
> work
> full
> impractical.
> to
a[vbcol=seagreen]
to[vbcol=seagreen]
> transaction
> most
> chances
needs[vbcol=seagreen]
> to
and[vbcol=seagreen]
have[vbcol=seagreen]
> 3
> performing
> FG3,
> tied
Primary[vbcol=seagreen]
> FG
>|||In addition to the other posts: In SQL Server 2005, you will not need to app
ly the tlog backups if
the file group has been read only since the backup you restored.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl.
.
> do i need the Tlogs to recover the db or just get it to point in time ? I
am
> not worried about getting it to the point in time .. If i restore the Jun
e
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> and
> logs
> be
> crashes
> the
> work
> full
> impractical.
> to
> transaction
> most
> chances
> to
> 3
> performing
> FG3,
> tied
> FG
>|||Hi Uri
Thanks for such a good example. I played around your example and landed on
one more question.
After the backup of database and transaction log stage I add more changes to
the Primary group table by running "insert test..test default values". I
backed up the primary file group files and transaction log files and I ran
following steps
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH NORECOVERY
RESTORE DATABASE test
FILE = 'test',
FILEGROUP = 'primary'
FROM DISK ='D:\CROUPFILES1.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
I do not see new changes made after the full backup
Why so'
Regards
Mangesh
All other steps being the same I loose changes made after backup in the
primary file.
Does it mean that you can use FG (filegroup backup ) as a means of driving
element in the database recovery. I though
"Uri Dimant" wrote:

> Hassan
> Yes , you have to do FULL BACKUP DATABASE and as Mike mentioned to perfor
m
> T-LOG BACKUP as well
> CREATE DATABASE test
> GO
> ALTER DATABASE test SET RECOVERY FULL
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
>
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
> insert test..test default values
> insert test..test_GR default values
> SELECT * FROM test..test_GR
> SELECT * FROM test..test
>
> BACKUP DATABASE test
> TO disk='D:\Test_backup.bak'with init
> BACKUP DATABASE test
> FILE = 'ww',
> FILEGROUP = 'ww_Group'
> TO disk='D:\CROUPFILES.bak'WITH INIT
> BACKUP LOG test
> TO disk='D:\Test__log.ldf'WITH INIT
> BACKUP LOG test
> TO disk='D:\Test__log.ldf' WITH NOINIT
> GO
> TRUNCATE TABLE test..test_GR
> GO
>
> RESTORE DATABASE test
> from disk='D:\Test_backup.bak'WITH NORECOVERY
>
> RESTORE DATABASE test
> FILE = 'ww',
> FILEGROUP = 'ww_Group'
> FROM DISK ='D:\CROUPFILES.bak'
> WITH FILE = 1,NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 1, NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 2, RECOVERY
> GO
> DROP DATABASE test
>
>
>
>
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> am
> June
> in
> be
> to
> So
> stage
> that
> a
> to
> needs
> and
> have
> Primary
>
>|||This works for me fine
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
insert test..test default values
insert test..test_GR default values
SELECT * FROM test..test_GR
SELECT * FROM test..test
BACKUP DATABASE test
TO disk='D:\Test_backup.bak'with init
BACKUP DATABASE test
FILE = 'test ',
FILEGROUP = 'primary'
TO disk='D:\CROUPFILES.bak'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf' WITH NOINIT
GO
TRUNCATE TABLE test..test_GR
GO
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH noRECOVERY
RESTORE DATABASE test
FILE = 'test',
FILEGROUP = 'primary'
FROM DISK ='D:\CROUPFILES.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
GO
DROP DATABASE test
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:4E9B24F7-8A13-43B6-BF4D-208FB115429E@.microsoft.com...
> Hi Uri
> Thanks for such a good example. I played around your example and landed
on
> one more question.
> After the backup of database and transaction log stage I add more changes
to
> the Primary group table by running "insert test..test default values". I
> backed up the primary file group files and transaction log files and I ran
> following steps
> RESTORE DATABASE test
> from disk='D:\Test_backup.bak'WITH NORECOVERY
> RESTORE DATABASE test
> FILE = 'test',
> FILEGROUP = 'primary'
> FROM DISK ='D:\CROUPFILES1.bak'
> WITH FILE = 1,NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 1, NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 2, RECOVERY
>
> I do not see new changes made after the full backup
> Why so'
> Regards
> Mangesh
>
> All other steps being the same I loose changes made after backup in the
> primary file.
> Does it mean that you can use FG (filegroup backup ) as a means of
driving[vbcol=seagreen]
> element in the database recovery. I though
>
>
>
> "Uri Dimant" wrote:
>
perform[vbcol=seagreen]
? I[vbcol=seagreen]
2004[vbcol=seagreen]
logs[vbcol=seagreen]
you.[vbcol=seagreen]
transaction[vbcol=seagreen]
pages[vbcol=seagreen]
would[vbcol=seagreen]
able[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
would[vbcol=seagreen]
state.[vbcol=seagreen]
does[vbcol=seagreen]
have[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
place[vbcol=seagreen]
Also,[vbcol=seagreen]
restoring[vbcol=seagreen]
forward[vbcol=seagreen]
other[vbcol=seagreen]
a[vbcol=seagreen]
log[vbcol=seagreen]
and[vbcol=seagreen]
T1[vbcol=seagreen]
active[vbcol=seagreen]