Filter Html tags on Full text Search

I have a Text field in my database that has data along with the HTML tags.
I dont want to search these HTML tags on my FUll text Search.
Example Data
< Font color='red'> Blah</Font>
I want to ignore the font tages on my search.
How do i do it?
save the content in the text data type columns into columns of the image
data type, and use the document type column with a value of htm so that only
the content of these files will be indexed.
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:3BF3659B-E3EB-495F-8D17-75DDD996E323@.microsoft.com...
>I have a Text field in my database that has data along with the HTML tags.
> I dont want to search these HTML tags on my FUll text Search.
> Example Data
> < Font color='red'> Blah</Font>
> I want to ignore the font tages on my search.
> How do i do it?
>
|||Can you please be more elaborate.I am indexing almost 9 columns of Text data
type.How do I save all these columns into columns of the image data type?
How do I use document type columns?
"Hilary Cotter" wrote:

> save the content in the text data type columns into columns of the image
> data type, and use the document type column with a value of htm so that only
> the content of these files will be indexed.
> "Bruce" <Bruce@.discussions.microsoft.com> wrote in message
> news:3BF3659B-E3EB-495F-8D17-75DDD996E323@.microsoft.com...
>
>
|||for the document type column you must
1) ensure you have a column which is char(3) or char(4) and contains the
value htm or .htm
2) store your html content in an image data type column
3) use sp_fulltext_column to specify that the document type is specified in
the document type column you created above in 1)
here is an example
sp_fulltext_column 'MyTable','ImageColumn', 'add', 1033,
'DocumentTypeColumn'
where MyTable is the table you are full text indexing, ImageColumn is a
column of the image datatype, and DocumentTypeColumn is the char(3) or
char(4) column which tells what the native type of the document you are
storing is.
Now, if you also might want to convert your docs to pure text. Using
FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it.
To convert your columns from html to text or from the text datatype to image
you should spit them out to the file system, and then convert them and push
them back.
Let me know if you need code samples to do this.
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:604B02B0-2741-402F-AE62-318452B9BDF0@.microsoft.com...
> Can you please be more elaborate.I am indexing almost 9 columns of Text
data[vbcol=seagreen]
> type.How do I save all these columns into columns of the image data type?
> How do I use document type columns?
>
> "Hilary Cotter" wrote:
only[vbcol=seagreen]
tags.[vbcol=seagreen]
|||Hilary,
"Now, if you also might want to convert your docs to pure text. Using
FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it."
Is the use of FiltDump in the above scenario a violation of Microsoft's
licensing agreement?
Thanks,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIB1eqrtEHA.2596@.TK2MSFTNGP10.phx.gbl...
> for the document type column you must
> 1) ensure you have a column which is char(3) or char(4) and contains the
> value htm or .htm
> 2) store your html content in an image data type column
> 3) use sp_fulltext_column to specify that the document type is specified
in
> the document type column you created above in 1)
> here is an example
> sp_fulltext_column 'MyTable','ImageColumn', 'add', 1033,
> 'DocumentTypeColumn'
>
> where MyTable is the table you are full text indexing, ImageColumn is a
> column of the image datatype, and DocumentTypeColumn is the char(3) or
> char(4) column which tells what the native type of the document you are
> storing is.
> Now, if you also might want to convert your docs to pure text. Using
> FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it.
> To convert your columns from html to text or from the text datatype to
image
> you should spit them out to the file system, and then convert them and
push[vbcol=seagreen]
> them back.
> Let me know if you need code samples to do this.
>
> "Bruce" <Bruce@.discussions.microsoft.com> wrote in message
> news:604B02B0-2741-402F-AE62-318452B9BDF0@.microsoft.com...
> data
type?[vbcol=seagreen]
image[vbcol=seagreen]
that
> only
> tags.
>
|||That's in interesting question. I'll have to check into it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OEpwKoFuEHA.2624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hilary,
> "Now, if you also might want to convert your docs to pure text. Using
> FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it."
> Is the use of FiltDump in the above scenario a violation of Microsoft's
> licensing agreement?
> Thanks,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIB1eqrtEHA.2596@.TK2MSFTNGP10.phx.gbl...
> in
> image
> push
Text[vbcol=seagreen]
> type?
> image
> that
HTML
>
|||Yes, as you had indicated in the past that any such use was not allowed by a
non-publicly available license policy for these files in another thread. If
you or Microsoft would make this licensing policy public, then there would
be less confusion on this issue.
Best Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#SNgrgWuEHA.3320@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> That's in interesting question. I'll have to check into it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OEpwKoFuEHA.2624@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
specified[vbcol=seagreen]
a[vbcol=seagreen]
are
> Text
> HTML
>
|||Specifically what I said in the past was that I had been advised by
Microsoft that you could not use the word breakers for your own purposes, ie
to roll your own hit highlighting solution.
Filtdump may be another matter, as it is a diagnostic tool.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%2344qClXuEHA.2828@.TK2MSFTNGP12.phx.gbl...
> Yes, as you had indicated in the past that any such use was not allowed by
> a
> non-publicly available license policy for these files in another thread.
> If
> you or Microsoft would make this licensing policy public, then there would
> be less confusion on this issue.
> Best Regards,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#SNgrgWuEHA.3320@.TK2MSFTNGP15.phx.gbl...
> the
> specified
> a
> are
>
|||You also "generalized" response to include ALL .dll and .exe files in
addition to the wordbreaker dll files, if memory servers me correctly. I
also asked you (or Microsoft) at that time to make public (and now again)
the specific licensing policy from Microsoft that you are referring to. If
it is a secret or under NDA, then how can anyone judge whether or not he or
she is violating a non-public licensing agreement.
Best Regards,
John
PS: Feel free to contact me off-line.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eyNhvefuEHA.3456@.TK2MSFTNGP10.phx.gbl...
> Specifically what I said in the past was that I had been advised by
> Microsoft that you could not use the word breakers for your own purposes,
ie[vbcol=seagreen]
> to roll your own hit highlighting solution.
> Filtdump may be another matter, as it is a diagnostic tool.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%2344qClXuEHA.2828@.TK2MSFTNGP12.phx.gbl...
by[vbcol=seagreen]
would[vbcol=seagreen]
Microsoft's[vbcol=seagreen]
contains[vbcol=seagreen]
is[vbcol=seagreen]
Using[vbcol=seagreen]
of[vbcol=seagreen]
data[vbcol=seagreen]
[vbcol=seagreen]
the
>
|||Please review the pertinent posts:
http://groups.google.com/groups?hl=e...ver .fulltext
http://groups.google.com/groups?hl=e...ver .fulltext
I am not trying to hide anything and AFAIK the communication was not under
NDA. I do not have the communication I had with the Microsoft developer, nor
do I have the response I received from the link I posted as I posted above.
The link I posted is regarding distributing dlls and exes, as you correctly
point out. When I asked another question about tapping into services exposed
by another Microsoft product for my commercial use, I was directed to this
link by a PSS engineer who explained this was the forum to ask these
questions to.
I'll follow up on filtdump and post back here with the response I get. I
will ask that it can be made public.
Please stop mischaracterizing what I say, or check the original posts before
commenting on them.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:#dD0fKhuEHA.2632@.TK2MSFTNGP10.phx.gbl...
> You also "generalized" response to include ALL .dll and .exe files in
> addition to the wordbreaker dll files, if memory servers me correctly. I
> also asked you (or Microsoft) at that time to make public (and now again)
> the specific licensing policy from Microsoft that you are referring to. If
> it is a secret or under NDA, then how can anyone judge whether or not he
or[vbcol=seagreen]
> she is violating a non-public licensing agreement.
> Best Regards,
> John
> PS: Feel free to contact me off-line.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eyNhvefuEHA.3456@.TK2MSFTNGP10.phx.gbl...
purposes,[vbcol=seagreen]
> ie
allowed[vbcol=seagreen]
> by
thread.[vbcol=seagreen]
> would
Using[vbcol=seagreen]
> Microsoft's
> contains
ImageColumn[vbcol=seagreen]
> is
char(3)[vbcol=seagreen]
you[vbcol=seagreen]
> Using
datatype[vbcol=seagreen]
them[vbcol=seagreen]
> of
> data
of[vbcol=seagreen]
htm[vbcol=seagreen]
> the
Search.
>

Fill factor

What fill factor value should I set for index on a table that is hit 10,000
times a day? Is there any other tool to help boost performance along with
fill factor?
Gracias
We'll need just a "bit" more information to answer that question. Does this
table have a clustered index? Is it autoincrementing? >Hit? What is the
percentage of reads to writes for the table? Plenty of available disk
space?
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> What fill factor value should I set for index on a table that is hit
> 10,000
> times a day? Is there any other tool to help boost performance along with
> fill factor?
> Gracias
|||A dbcc showcontig (tableName) is also useful. Could you put that out here as
well.
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>
|||Hi Jerry,
Thanks a lot for responding.
Table has primary key clustered index w/ fill factor 90%, autogrow 10%, 90%
write, plenty of disk space.
LaEsmeralda
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>
|||That is still not enough info to make an intelligent decision. The fill
factor is used mainly to limit the number of page splits that occur in an
index. This is influenced by the column(s) in the clustered index (in your
case), the number of inserts or updates to the clustered key and the values
of the column(s) of the clustered key. If the key is an identity and you
never update the row with data that is larger than the existing data you can
get by with a 100% fill factor as each new row is appended to the end of the
page. If the values are random such as last name then you have to account
for how much space should be there in between index rebuilds so that you get
few to no page splits. But a clustered index is not the only factor. Too
many non clustered indexes can be an issue with lots of writes as well.
Please post the DDL for the table including all indexes, constraints etc.
Andrew J. Kelly SQL MVP
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
|||I think it still depends. Is this an autoincrementing PK? If so new
records will be appended to the end of the table. If not, new records could
get inserted anywhere. Are there a lot of updates occuring?
As a general rule of thumb I think tables that are predominately read should
have a higher FILLFACTOR to reduce the number of data pages required to
store the data and the amount of physical I/O required to process queries.
A lower FILLFACTOR would apply for a table with a large number of writes.
This approach would help minimize internal and external fragementation but
at the cost of increased disk space to store the data and possibly a reduced
query performance.
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:

Fill factor

What fill factor value should I set for index on a table that is hit 10,000
times a day? Is there any other tool to help boost performance along with
fill factor?
GraciasWe'll need just a "bit" more information to answer that question. Does this
table have a clustered index? Is it autoincrementing? >Hit? What is the
percentage of reads to writes for the table? Plenty of available disk
space?
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> What fill factor value should I set for index on a table that is hit
> 10,000
> times a day? Is there any other tool to help boost performance along with
> fill factor?
> Gracias|||A dbcc showcontig (tableName) is also useful. Could you put that out here as
well.
"Jerry Spivey" wrote:
> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> > What fill factor value should I set for index on a table that is hit
> > 10,000
> > times a day? Is there any other tool to help boost performance along with
> > fill factor?
> > Gracias
>
>|||Hi Jerry,
Thanks a lot for responding.
Table has primary key clustered index w/ fill factor 90%, autogrow 10%, 90%
write, plenty of disk space.
LaEsmeralda
"Jerry Spivey" wrote:
> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> > What fill factor value should I set for index on a table that is hit
> > 10,000
> > times a day? Is there any other tool to help boost performance along with
> > fill factor?
> > Gracias
>
>|||That is still not enough info to make an intelligent decision. The fill
factor is used mainly to limit the number of page splits that occur in an
index. This is influenced by the column(s) in the clustered index (in your
case), the number of inserts or updates to the clustered key and the values
of the column(s) of the clustered key. If the key is an identity and you
never update the row with data that is larger than the existing data you can
get by with a 100% fill factor as each new row is appended to the end of the
page. If the values are random such as last name then you have to account
for how much space should be there in between index rebuilds so that you get
few to no page splits. But a clustered index is not the only factor. Too
many non clustered indexes can be an issue with lots of writes as well.
Please post the DDL for the table including all indexes, constraints etc.
--
Andrew J. Kelly SQL MVP
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>> We'll need just a "bit" more information to answer that question. Does
>> this
>> table have a clustered index? Is it autoincrementing? >Hit? What is
>> the
>> percentage of reads to writes for the table? Plenty of available disk
>> space?
>> HTH
>> Jerry
>> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
>> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>> > What fill factor value should I set for index on a table that is hit
>> > 10,000
>> > times a day? Is there any other tool to help boost performance along
>> > with
>> > fill factor?
>> > Gracias
>>|||I think it still depends. Is this an autoincrementing PK? If so new
records will be appended to the end of the table. If not, new records could
get inserted anywhere. Are there a lot of updates occuring?
As a general rule of thumb I think tables that are predominately read should
have a higher FILLFACTOR to reduce the number of data pages required to
store the data and the amount of physical I/O required to process queries.
A lower FILLFACTOR would apply for a table with a large number of writes.
This approach would help minimize internal and external fragementation but
at the cost of increased disk space to store the data and possibly a reduced
query performance.
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>> We'll need just a "bit" more information to answer that question. Does
>> this
>> table have a clustered index? Is it autoincrementing? >Hit? What is
>> the
>> percentage of reads to writes for the table? Plenty of available disk
>> space?
>> HTH
>> Jerry
>> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
>> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>> > What fill factor value should I set for index on a table that is hit
>> > 10,000
>> > times a day? Is there any other tool to help boost performance along
>> > with
>> > fill factor?
>> > Gracias
>>

Fill factor

What fill factor value should I set for index on a table that is hit 10,000
times a day? Is there any other tool to help boost performance along with
fill factor?
GraciasWe'll need just a "bit" more information to answer that question. Does this
table have a clustered index? Is it autoincrementing? >Hit? What is the
percentage of reads to writes for the table? Plenty of available disk
space?
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> What fill factor value should I set for index on a table that is hit
> 10,000
> times a day? Is there any other tool to help boost performance along with
> fill factor?
> Gracias|||A dbcc showcontig (tableName) is also useful. Could you put that out here a
s
well.
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does th
is
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>|||Hi Jerry,
Thanks a lot for responding.
Table has primary key clustered index w/ fill factor 90%, autogrow 10%, 90%
write, plenty of disk space.
LaEsmeralda
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does th
is
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>|||That is still not enough info to make an intelligent decision. The fill
factor is used mainly to limit the number of page splits that occur in an
index. This is influenced by the column(s) in the clustered index (in your
case), the number of inserts or updates to the clustered key and the values
of the column(s) of the clustered key. If the key is an identity and you
never update the row with data that is larger than the existing data you can
get by with a 100% fill factor as each new row is appended to the end of the
page. If the values are random such as last name then you have to account
for how much space should be there in between index rebuilds so that you get
few to no page splits. But a clustered index is not the only factor. Too
many non clustered indexes can be an issue with lots of writes as well.
Please post the DDL for the table including all indexes, constraints etc.
Andrew J. Kelly SQL MVP
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>|||I think it still depends. Is this an autoincrementing PK? If so new
records will be appended to the end of the table. If not, new records could
get inserted anywhere. Are there a lot of updates occuring?
As a general rule of thumb I think tables that are predominately read should
have a higher FILLFACTOR to reduce the number of data pages required to
store the data and the amount of physical I/O required to process queries.
A lower FILLFACTOR would apply for a table with a large number of writes.
This approach would help minimize internal and external fragementation but
at the cost of increased disk space to store the data and possibly a reduced
query performance.
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>

fill down formulas without selecting the sheet

At the moment I have a bunch of formulas along the top row of a sheet and I
wish to copy them down a predfined number of rows. At the moment I activate
the sheet and then select the rows I want to copy down with the row
containing the formulas at the top. It would be much er if I could
accomplish this in the background as it were without having to select the
sheet. Is there a way of effectively filling down the formula without having
to activate the sheet?
Any help much appreciated, kind regards, MarkAre you sure you posted in the right NG ? That should go for the excel ng as
I hear from your problem ?!
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mark Stephens" wrote:

> At the moment I have a bunch of formulas along the top row of a sheet and
I
> wish to copy them down a predfined number of rows. At the moment I activat
e
> the sheet and then select the rows I want to copy down with the row
> containing the formulas at the top. It would be much er if I could
> accomplish this in the background as it were without having to select the
> sheet. Is there a way of effectively filling down the formula without havi
ng
> to activate the sheet?
> Any help much appreciated, kind regards, Mark
>
>

Filegroup Backups

I seem to be having an issue with file groups and differential backups. I
have a database with multiple filegoups. If I do a filegoup backups along
with a differential backup, I cannot restore the differential backup. I get
the following error:
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 827000000462900035, which is too
recent to apply to the database. An earlier log backup that includes LSN
827000000460600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I have tried filegoup as well as database differential backups with the same
result. Restoring logs seems to work fine, but in my scenario, I donâ't want
to rely on 1 week or a month of transaction logs depending on how often I do
a full backup on each filegoup. First question, are differential backups
supported with filegroups? If so, is there a special way to do the
differential backup/restore?
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Primary'
TO DISK = N'c:\Primary.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Doc1'
TO DISK = N'c:\Doc1.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
TO DISK = N'c:\diff.diff'
WITH NOFORMAT, DIFFERENTIAL
BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
TO DISK = N'c:\partition.diff'
WITH NOFORMAT, DIFFERENTIAL
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
FROM DISK = 'c:\Primary.bak'
with move 'PartitionTest' to 'c:\partitiontest2.mdf'
,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
,PARTIAL,norecovery,REPLACE
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
FROM DISK = 'c:\doc1.bak'
with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
,standby = 'f:\standby.bak',REPLACE
-- this will error out
RESTORE log [PartitionTest_Restore]
FROM DISK = 'c:\diff.diff'
with norecovery
-- Same error.
RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
FROM DISK = 'c:\diff.diff'
with norecoveryDave B,
To restore a differential backup, you have to use "restore database"
statement instead "restore log". Also, when restoring multiple backups, you
need to use option "with norecovery" except in the final restore.
Check "restore database" in BOL for more info.
AMB
"Dave B" wrote:
> I seem to be having an issue with file groups and differential backups. I
> have a database with multiple filegoups. If I do a filegoup backups along
> with a differential backup, I cannot restore the differential backup. I get
> the following error:
> Msg 4305, Level 16, State 1, Line 1
> The log in this backup set begins at LSN 827000000462900035, which is too
> recent to apply to the database. An earlier log backup that includes LSN
> 827000000460600001 can be restored.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> I have tried filegoup as well as database differential backups with the same
> result. Restoring logs seems to work fine, but in my scenario, I donâ't want
> to rely on 1 week or a month of transaction logs depending on how often I do
> a full backup on each filegoup. First question, are differential backups
> supported with filegroups? If so, is there a special way to do the
> differential backup/restore?
>
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Primary'
> TO DISK = N'c:\Primary.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Doc1'
> TO DISK = N'c:\Doc1.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> TO DISK = N'c:\diff.diff'
> WITH NOFORMAT, DIFFERENTIAL
> BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
> TO DISK = N'c:\partition.diff'
> WITH NOFORMAT, DIFFERENTIAL
>
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> FROM DISK = 'c:\Primary.bak'
> with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> ,PARTIAL,norecovery,REPLACE
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> FROM DISK = 'c:\doc1.bak'
> with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> ,standby = 'f:\standby.bak',REPLACE
> -- this will error out
> RESTORE log [PartitionTest_Restore]
> FROM DISK = 'c:\diff.diff'
> with norecovery
> -- Same error.
> RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> FROM DISK = 'c:\diff.diff'
> with norecovery
>|||Thanks.
"Alejandro Mesa" wrote:
> Dave B,
> To restore a differential backup, you have to use "restore database"
> statement instead "restore log". Also, when restoring multiple backups, you
> need to use option "with norecovery" except in the final restore.
> Check "restore database" in BOL for more info.
> AMB
> "Dave B" wrote:
> > I seem to be having an issue with file groups and differential backups. I
> > have a database with multiple filegoups. If I do a filegoup backups along
> > with a differential backup, I cannot restore the differential backup. I get
> > the following error:
> >
> > Msg 4305, Level 16, State 1, Line 1
> > The log in this backup set begins at LSN 827000000462900035, which is too
> > recent to apply to the database. An earlier log backup that includes LSN
> > 827000000460600001 can be restored.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE LOG is terminating abnormally.
> >
> > I have tried filegoup as well as database differential backups with the same
> > result. Restoring logs seems to work fine, but in my scenario, I donâ't want
> > to rely on 1 week or a month of transaction logs depending on how often I do
> > a full backup on each filegoup. First question, are differential backups
> > supported with filegroups? If so, is there a special way to do the
> > differential backup/restore?
> >
> >
> > BACKUP DATABASE [PartitionTest]
> > FILEGROUP = N'Primary'
> > TO DISK = N'c:\Primary.bak'
> > WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> > NOREWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [PartitionTest]
> > FILEGROUP = N'Doc1'
> > TO DISK = N'c:\Doc1.bak'
> > WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> > NOREWIND, NOUNLOAD, STATS = 10
> > GO
> >
> > BACKUP DATABASE [PartitionTest]
> > TO DISK = N'c:\diff.diff'
> > WITH NOFORMAT, DIFFERENTIAL
> >
> > BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
> > TO DISK = N'c:\partition.diff'
> > WITH NOFORMAT, DIFFERENTIAL
> >
> >
> > RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> > FROM DISK = 'c:\Primary.bak'
> > with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> > ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> > ,PARTIAL,norecovery,REPLACE
> >
> > RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> > FROM DISK = 'c:\doc1.bak'
> > with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> > ,standby = 'f:\standby.bak',REPLACE
> >
> > -- this will error out
> > RESTORE log [PartitionTest_Restore]
> > FROM DISK = 'c:\diff.diff'
> > with norecovery
> >
> > -- Same error.
> > RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> > FROM DISK = 'c:\diff.diff'
> > with norecovery
> >
> >