Full text indexes files missing?

I have a full text index...in the properties of the index the path
shows...
C:\Program Files\Microsoft SQL Server\MSSQL\FTData
But if I look in the directory...I see nothing.
Yet...the system still lets me repopulate the index.
Is this normal? Should the index files be in this directory?
I searched the system for the .gthr files...and It shows them on the
D:\MSSQL\FTDATA directory.
This seems odd to me.
They don't have to be in this directory, issue the following stored
procedure to find out where your files are
sp_help_fulltext_catalogs
Note the path column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<mchi55@.hotmail.com> wrote in message
news:1168226858.291120.304230@.11g2000cwr.googlegro ups.com...
>I have a full text index...in the properties of the index the path
> shows...
> C:\Program Files\Microsoft SQL Server\MSSQL\FTData
>
> But if I look in the directory...I see nothing.
> Yet...the system still lets me repopulate the index.
> Is this normal? Should the index files be in this directory?
> I searched the system for the .gthr files...and It shows them on the
> D:\MSSQL\FTDATA directory.
> This seems odd to me.
>

Full text indexes files missing?

I have a full text index...in the properties of the index the path
shows...
C:\Program Files\Microsoft SQL Server\MSSQL\FTData
But if I look in the directory...I see nothing.
Yet...the system still lets me repopulate the index.
Is this normal? Should the index files be in this directory?
I searched the system for the .gthr files...and It shows them on the
D:\MSSQL\FTDATA directory.
This seems odd to me.They don't have to be in this directory, issue the following stored
procedure to find out where your files are
sp_help_fulltext_catalogs
Note the path column.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<mchi55@.hotmail.com> wrote in message
news:1168226858.291120.304230@.11g2000cwr.googlegroups.com...
>I have a full text index...in the properties of the index the path
> shows...
> C:\Program Files\Microsoft SQL Server\MSSQL\FTData
>
> But if I look in the directory...I see nothing.
> Yet...the system still lets me repopulate the index.
> Is this normal? Should the index files be in this directory?
> I searched the system for the .gthr files...and It shows them on the
> D:\MSSQL\FTDATA directory.
> This seems odd to me.
>

Full text indexes files missing?

I have a full text index...in the properties of the index the path
shows...
C:\Program Files\Microsoft SQL Server\MSSQL\FTData
But if I look in the directory...I see nothing.
Yet...the system still lets me repopulate the index.
Is this normal? Should the index files be in this directory?
I searched the system for the .gthr files...and It shows them on the
D:\MSSQL\FTDATA directory.
This seems odd to me.They don't have to be in this directory, issue the following stored
procedure to find out where your files are
sp_help_fulltext_catalogs
Note the path column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<mchi55@.hotmail.com> wrote in message
news:1168226858.291120.304230@.11g2000cwr.googlegroups.com...
>I have a full text index...in the properties of the index the path
> shows...
> C:\Program Files\Microsoft SQL Server\MSSQL\FTData
>
> But if I look in the directory...I see nothing.
> Yet...the system still lets me repopulate the index.
> Is this normal? Should the index files be in this directory?
> I searched the system for the .gthr files...and It shows them on the
> D:\MSSQL\FTDATA directory.
> This seems odd to me.
>

Full Text index on a view.

Am I missing something here? SQL Server 2005 BOL states that one the
requirements for the CREATE INDEX on a view are:
"The view cannot include text, ntext, or image columns, even if they
are not referenced in the CREATE INDEX statement".
But to create a Full Text index on the text column referenced in the
view I need a unique clustered index that I can't create because the
view references a text column.
Vicious circles.
It must be on an indexed view. Here is an example of how to create one.
CREATE DATABASE CODE_SAMPLE4
GO
USE CODE_SAMPLE4
GO
CREATE TABLE CODE_SAMPLE4
(PK INT NOT NULL IDENTITY CONSTRAINT PRIMARYKEY PRIMARY KEY,
CHARCOL CHAR(20),
INTCOL INT DEFAULT 1)
GO
DECLARE @.INT INT
SET @.INT=1
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL) VALUES('TEST')
SELECT @.INT=@.INT+1
END
SET @.INT=0
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL,INTCOL) VALUES('TEST',@.INT+1)
SELECT @.INT=@.INT+1
END
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4 (CHARCOL) KEY INDEX PRIMARYKEY
GO
--below query erroneously does not return any rows
SELECT * FROM CODE_SAMPLE4 AS CS4 JOIN
CONTAINSTABLE (CODE_SAMPLE4,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
CREATE VIEW CODE_SAMPLE4_VIEW WITH SCHEMABINDING
AS
SELECT PK, CHARCOL, INTCOL FROM DBO.CODE_SAMPLE4 WHERE INTCOL>10
GO
CREATE UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
CODE_SAMPLE4_VIEW(PK)
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4_VIEW (CHARCOL) KEY INDEX
CODE_SAMPLE4_VIEW_INDEX
GO
--below query returns all rows
SELECT * FROM CODE_SAMPLE4_VIEW AS CS4 JOIN CONTAINSTABLE
(CODE_SAMPLE4_VIEW,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
SELECT * FROM CODE_SAMPLE4_VIEW WHERE CONTAINS(*,'TEST')
GO
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <sqldba@.comcast.net> wrote in message
news:1182543834.843179.270800@.x35g2000prf.googlegr oups.com...
> Am I missing something here? SQL Server 2005 BOL states that one the
> requirements for the CREATE INDEX on a view are:
> "The view cannot include text, ntext, or image columns, even if they
> are not referenced in the CREATE INDEX statement".
> But to create a Full Text index on the text column referenced in the
> view I need a unique clustered index that I can't create because the
> view references a text column.
> Vicious circles.
>
|||Hilary,
That makes perfect sence but change the CHARCOL CHAR(20) to a data
type of Text and it doesn't workr. All is fine untill you CREATE
UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
CODE_SAMPLE4_VIEW(PK)
then it errors with:
Server: Msg 1942, Level 16, State 1, Line 1
Index cannot be created on view 'CODE_SAMPLE4_VIEW' because the view
contains text, ntext or image columns.
Lee.
|||Text is deprecated in SQL 2005. Change it to varchar(max) and it works.
CREATE DATABASE CODE_SAMPLE4
GO
USE CODE_SAMPLE4
GO
CREATE TABLE CODE_SAMPLE4
(PK INT NOT NULL IDENTITY CONSTRAINT PRIMARYKEY PRIMARY KEY,
CHARCOL varCHAR(max),
INTCOL INT DEFAULT 1)
GO
DECLARE @.INT INT
SET @.INT=1
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL) VALUES('TEST')
SELECT @.INT=@.INT+1
END
SET @.INT=0
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL,INTCOL) VALUES('TEST',@.INT+1)
SELECT @.INT=@.INT+1
END
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4 (CHARCOL) KEY INDEX PRIMARYKEY
GO
--below query erroneously does not return any rows
SELECT * FROM CODE_SAMPLE4 AS CS4 JOIN
CONTAINSTABLE (CODE_SAMPLE4,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
CREATE VIEW CODE_SAMPLE4_VIEW WITH SCHEMABINDING
AS
SELECT PK, CHARCOL, INTCOL FROM DBO.CODE_SAMPLE4 WHERE INTCOL>10
GO
CREATE UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
CODE_SAMPLE4_VIEW(PK)
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4_VIEW (CHARCOL) KEY INDEX
CODE_SAMPLE4_VIEW_INDEX
GO
--below query returns all rows
SELECT * FROM CODE_SAMPLE4_VIEW AS CS4 JOIN CONTAINSTABLE
(CODE_SAMPLE4_VIEW,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
SELECT * FROM CODE_SAMPLE4_VIEW WHERE CONTAINS(*,'TEST')
GO
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <sqldba@.comcast.net> wrote in message
news:1182777066.943787.176260@.n60g2000hse.googlegr oups.com...
> Hilary,
> That makes perfect sence but change the CHARCOL CHAR(20) to a data
> type of Text and it doesn't workr. All is fine untill you CREATE
> UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
> CODE_SAMPLE4_VIEW(PK)
> then it errors with:
> Server: Msg 1942, Level 16, State 1, Line 1
> Index cannot be created on view 'CODE_SAMPLE4_VIEW' because the view
> contains text, ntext or image columns.
> Lee.
>
|||How would you handle the same situation in SQL 2000 with a text column
that would need to remain a Text column?
|||I thought we were talking SQL 2005. In SQL 2000 you can't full-text index a
view.
Am I missing something here?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <sqldba@.comcast.net> wrote in message
news:1182785285.821430.47320@.u2g2000hsc.googlegrou ps.com...
> How would you handle the same situation in SQL 2000 with a text column
> that would need to remain a Text column?
>
|||No, your okay. I had ask about SQL 2005 and then SQL 2000. As for not
being able to ful-text a view. Guess I should have cought that.

Full backup to a different file name

I may be missing something here but is it possible to do a full database
backup each night but to a different file name so as not to overwrite the
existing full backup. The database is relatively small (200Meg) and we have
lots of hard drive space available. Of course we will need to manually
delete the old backups occasionally but we are happy to do that.
I know that I can do either a differential backup or a transaction log
backup but this will make the restoration process slower. Often we just
want to quickly restore a a database fron two days ago just to check we did
not screw something up.
Should I be looking towards 3rd party software?
Dave A
Dave
You can call a backup whatever you like. So yes you can call it what you
like. One of the easiest ways to do it, is to give it a base name and add the
date to it. This creates a file something like mydatabase_ddmmyy.bak (or
mydatabase_mmddyy.bak for our American friends)
Hope this helps
John
"Dave A" wrote:

> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>
|||John,
I want to schedule the backup to run everynight automatically. I don't want
to retype the name in each time. Sorry, I should have been more clear.
Regards
Dave A
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the[vbcol=seagreen]
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
> Hope this helps
> John
> "Dave A" wrote:
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]
|||The easiest way to do this is to set up a Maintenance Plan ... The
maintenance plan wizard will create a unique name with the date and time
automatically..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:OuF4BYgAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we
have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we
did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>
|||"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
You can use a maintenance job that handles this or roll your own code.
And here in America I prefer YYYYMMDD :-)
Just use datepart to assemble the file name.
[vbcol=seagreen]
> Hope this helps
> John
> "Dave A" wrote:
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]

Full backup to a different file name

I may be missing something here but is it possible to do a full database
backup each night but to a different file name so as not to overwrite the
existing full backup. The database is relatively small (200Meg) and we have
lots of hard drive space available. Of course we will need to manually
delete the old backups occasionally but we are happy to do that.
I know that I can do either a differential backup or a transaction log
backup but this will make the restoration process slower. Often we just
want to quickly restore a a database fron two days ago just to check we did
not screw something up.
Should I be looking towards 3rd party software?
Dave ADave
You can call a backup whatever you like. So yes you can call it what you
like. One of the easiest ways to do it, is to give it a base name and add th
e
date to it. This creates a file something like mydatabase_ddmmyy.bak (or
mydatabase_mmddyy.bak for our American friends)
Hope this helps
John
"Dave A" wrote:

> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we ha
ve
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we di
d
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||John,
I want to schedule the backup to run everynight automatically. I don't want
to retype the name in each time. Sorry, I should have been more clear.
Regards
Dave A
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the[vbcol=seagreen]
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
> Hope this helps
> John
> "Dave A" wrote:
>
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]|||The easiest way to do this is to set up a Maintenance Plan ... The
maintenance plan wizard will create a unique name with the date and time
automatically..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:OuF4BYgAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we
have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we
did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
You can use a maintenance job that handles this or roll your own code.
And here in America I prefer YYYYMMDD :-)
Just use datepart to assemble the file name.
[vbcol=seagreen]
> Hope this helps
> John
> "Dave A" wrote:
>
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]

Full backup to a different file name

I may be missing something here but is it possible to do a full database
backup each night but to a different file name so as not to overwrite the
existing full backup. The database is relatively small (200Meg) and we have
lots of hard drive space available. Of course we will need to manually
delete the old backups occasionally but we are happy to do that.
I know that I can do either a differential backup or a transaction log
backup but this will make the restoration process slower. Often we just
want to quickly restore a a database fron two days ago just to check we did
not screw something up.
Should I be looking towards 3rd party software?
Dave ADave
You can call a backup whatever you like. So yes you can call it what you
like. One of the easiest ways to do it, is to give it a base name and add the
date to it. This creates a file something like mydatabase_ddmmyy.bak (or
mydatabase_mmddyy.bak for our American friends)
Hope this helps
John
"Dave A" wrote:
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||John,
I want to schedule the backup to run everynight automatically. I don't want
to retype the name in each time. Sorry, I should have been more clear.
Regards
Dave A
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
> Hope this helps
> John
> "Dave A" wrote:
> > I may be missing something here but is it possible to do a full database
> > backup each night but to a different file name so as not to overwrite
the
> > existing full backup. The database is relatively small (200Meg) and we
have
> > lots of hard drive space available. Of course we will need to manually
> > delete the old backups occasionally but we are happy to do that.
> >
> > I know that I can do either a differential backup or a transaction log
> > backup but this will make the restoration process slower. Often we just
> > want to quickly restore a a database fron two days ago just to check we
did
> > not screw something up.
> >
> > Should I be looking towards 3rd party software?
> >
> > Dave A
> >
> >
> >
> >|||The easiest way to do this is to set up a Maintenance Plan ... The
maintenance plan wizard will create a unique name with the date and time
automatically..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:OuF4BYgAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we
have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we
did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
You can use a maintenance job that handles this or roll your own code.
And here in America I prefer YYYYMMDD :-)
Just use datepart to assemble the file name.
> Hope this helps
> John
> "Dave A" wrote:
> > I may be missing something here but is it possible to do a full database
> > backup each night but to a different file name so as not to overwrite
the
> > existing full backup. The database is relatively small (200Meg) and we
have
> > lots of hard drive space available. Of course we will need to manually
> > delete the old backups occasionally but we are happy to do that.
> >
> > I know that I can do either a differential backup or a transaction log
> > backup but this will make the restoration process slower. Often we just
> > want to quickly restore a a database fron two days ago just to check we
did
> > not screw something up.
> >
> > Should I be looking towards 3rd party software?
> >
> > Dave A
> >
> >
> >
> >