Is it possible to do a full restore of a database without restoring the full-text catalogs? At the end of each month, we restore a copy of a database for quick access to month-end data with a different name. e.g. we restore the database DBN as DBN1107. Since upgrading to SQL 2005, the restore is failing because it it trying to restore the full-text catalog.
View 2 RepliesWe are trying to restore from a backup (.bak) of a sql 2005 database with full text catologs to sql 2012.
We are getting errors saying that the backup file context are different from what we are trying to restore to, even if we try to create a new database from the backup.The new database is just a test database. The source is a production database with full cataologs.
The normal restore from a bak file does not seem to work.
We dont need the full text catalogs in the new database, but cannot change settings in the source production database.
Is there a way to take a backup of the sql 2005 database without the full text cataologs being included ? , without changing setting in the source database beforehand ?
Hi,
I have visual studio 2005 with the sql server express edition that is installed by default when installing vs2005.
I am used to work from within vs when I want to create and work with databases for my web sites (I dont have visual manager installed).
I would like to enable full text search on a table's column.
Is there an easy way to do that from within vs or is it necessary to install another copy of sql server express with advanced services as well as sql server managment studio express?
How can I know if full text search is enabled?
Thanx in advance!
I am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.
'Open sql connection
SqlConnection1.Open()
Dim datareader2 As SqlClient.SqlDataReader
datareader2 = cmdFindRowsWithKeyword.ExecuteReader
Dim strMsg As String
Dim intRowToFlag As Integer
Dim strRowsToGet As String
Dim strKeywordAsTyped As String
Dim strKeywordAllCaps As String
Dim strKeywordAllLower As String
Dim strKeywordFirstLetterCap As String
Dim FirstLetter As String
While datareader2.Read
intRowToFlag = datareader2(0).ToString
strMsg = datareader2(1).ToString
'Assign keyword as typed to variable
strKeywordAsTyped = txtSearchFor.Text
'Assign keyword as typed to variable then convert it to all uppercase
strKeywordAllCaps = txtSearchFor.Text
strKeywordAllCaps = strKeywordAllCaps.ToUpper
'Assign keyword as typed to variable then convert it to all lowercase
strKeywordAllLower = txtSearchFor.Text
strKeywordAllLower = strKeywordAllLower.ToLower
'Assign keyword as typed to variable then convert it so just the first letter is in uppercase
strKeywordFirstLetterCap = txtSearchFor.Text
FirstLetter = strKeywordFirstLetterCap.Chars(0)
FirstLetter = FirstLetter.ToUpper
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1)
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)
'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row.
If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then
cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag
SqlConnection2.Open()
Dim datareader3 As SqlClient.SqlDataReader
datareader3 = cmdFlagRowsWithKeyword.ExecuteReader
datareader3.Close()
SqlConnection2.Close()
End If
End While
datareader2.Close()
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
After I restore a database with SQL the database I cannot access it. However, after restoring using Enterprise Manager I do not have the same problem. The message I get is:
Database 'les_test' cannot be opened. It is in the middle of a restore.
The code I am trying to use for the restore is:
RESTORE DATABASE les_test
FROM DISK = 'c:MSSQL7BACKUPiztrobeta_db_200012011201.BAK'
WITH NORECOVERY,
REPLACE,
MOVE 'biztrobeta_PRI' TO 'c:mssql7DATAiztrobeta_PRI.ndf',
MOVE 'biztrobeta_FGE_Dat1' TO 'c:mssql7DATAiztrobeta_FGE_Dat1.ndf',
MOVE 'biztrobeta_FGX_Idx1 ' TO 'c:mssql7DATAiztrobeta_FGX_Idx1.ndf',
MOVE 'biztrobeta_LOG' TO 'c:mssql7Dataiztrobeta_LOG.ldf'
GO
I tried changing the status in the master..sysdatabases tables, but the restored database was still not accessible. I tried to do this using the following statment:
UPDATE sysdatabases
SET STATUS = 4194316
WHERE name = 'les_test'
GO
Any help is appreciated.
Thanks,
Les
Hello everyone,
I've got a little question - wich drives me absolutely nuts by now :).
I'm trying to optimize my full-text search since a while now.
Scenario:
Full-Text Table:
id - int - primary key
srcID - varchar(30)
srchField - varchar(150) - FULL-TEXT INDEXED
src - varchar(15)
updateStamp - timestamp
Records in Table: ~ 4480000
To get my results I have to link the srcID-Field to another table.
But for performance Testing, two queries
LIKE VERSION:
SELECT srcID FROM fulltexttable WHERE srchField LIKE '%an%' GROUP BY srcID ODER BY srcID DESC
1. Running Time: 5 seconds, returns 921315 rows
2. Running Time: 5 seconds, returns 921315 rows
FT VERSION:
SELECT srcID FROM fulltexttable WHERE CONTAINS(srchField,' "an*" ') GROUP BY srcID ODER BY srcID DESC
1. Running Time: 14 seconds, returns 443676 rows
2. Running Time: 8 seconds, returns 443676 rows
If i do a search wich returns less records, full-text is a lot faster - but on big recordcounts it seems the LIKE query is faster? Is that possible???
Anyone any ideas?
Thanks in advance
Database is OK. I just need to roll back all the transactions until certainpoint in time. How to do it?
View 2 Replies View RelatedHello,I am learning SQL Server 2005 Expres Edition. I need to create fulltext index on Books.Remarks column in my database. I tried:CREATE FULLTEXT CATALOG FTC_Books AS DEFAULTCREATE FULLTEXT INDEX ON Books (Remarks) KEY INDEX IX_Books_Remarks ONFTC_BooksUnfortunately I got error:Full-Text Search is not installed, or a full-text component cannot beloaded.I don't understand this because I marked all options during setup.Please help/RAM/
View 5 Replies View RelatedHiya,
I have recently become responsible for a small database for a volunteer soccer league. I am reasonably savvy when it comes to development, but I have not had a lot of experience with administration before.
I need to do what I think must be pretty simple: set up full text indexing so I can use a CONTAINS search on a table. The table contains all of the fields the kids use, and each field has a number of divisions that typically play on that field; we use these 'favored divisions' to make scheduling a little easier. Now, one day when I have time, I will set up a proper, normalized, one-to-many relationship between the favored divisions and the playing fields, but right now it's basically like this:
fieldID (int, primary key, identity seed)
fieldName (varchar), e.g. High School Field
favored_divisions (varchar) - comma-delimited list of divisions, e.g. G10,B14,G12
I imagine it's probably database sacrilege to have a comma-delimited list like that, but we don't have the resources now to re-write that piece of the web application. My question is, in SQL Server 2005, what do I need to do to be able to do a full-text search on this field with the following query:
SELECT fieldID, fieldName
FROM playing_fields
WHERE CONTAINS(favored_divisions,'G10')
Right now the query runs and does not return an error, but does not return any results, either. IIRC, full-text indexing is enabled by default in SQL Server 2005, but I am not familiar with the procedure -- something about having to populate a catalog. Do I need to edit or set up a new index on the actual playing_fields table? What has to happen to make this work?
Thanks very much,
Sam
I am following the how to in the sql studio management express and for text indexing it says
================================================
To enable a database for full-text indexing
In Object Explorer, expand the server group, right-click the database for which you want to enable full-text indexing, and then select Properties.
Select the Files page, and then select Use full-text indexing
This worked ok
==================================================
next it says to
==================================================
To enable a table for full-text indexing
Expand the server group, expand Databases, expand User Databases, and expand the database that contains the table you want to enable for full-text indexing.
Right-click the table that you want to enable for full-text indexing.
Select Full-Text index, and then click Enable Full-Text indexing.
in object explorer ,do right-click on database and is selecting preoperties and is selecting "files" page "use full-text indexing" ckeck box is disable.
how can enabled this check box?
thanks , mohsen
Hello !
When I try to search into a database table I get the following error :
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Customers' because it is not full-text indexed.
Please tell me how can I make the index.
I am using SQL Server Express 2005,VWD 2008 and WIN XP.
Thank You !
Hi Everybody,
Since SQL 2005 express doesn't support full text search... is there any third party that provides FTS for the express version?
Thank YOU
Hi,
I am putting this question here but I am not limiting it to sql server 2005 express edition.
I am developing an app on a local machine (winxp with sql server 2000 personal edition) however I came to find out that full-text does not work in this setup unless I use a server type machine.
This fouls up my development somewhat and I would like to know if there is
a) a work around for my sql server 200 setup
b) does full-text serach work in sql server 2005 express edition which I have installed on my PC ?
Thanks for any input on this issue.
Tuka
Full text search is the search functionality that enables a fast and easy way of searching text based data in SQL Server. Although the concept of full text search is not new in SQL Server 2005, there are considerable enhancements in performance and manageability of SQL Server 2005. http://aspalliance.com/1512_Understanding_Full_Text_Search_in_SQL_Server_2005.all
Uday Denduluri
Software Engineer
Refer my articles at http://aspalliance.com/author.aspx?uId=62740
Hello:
I am on a development machine with sql 2000 & sql 2005 installed. The full Text Search for sql 2005 is installed and running on this local machine.
However, I need to do a full text search with sql 2000. When I do:
sp_fulltext_database 'enable'
I get:
Full-Text Search is not installed, or a full-text component cannot be loaded.
Can I install full text search for both sql server 2000 & 2005. I read somewhere that this is not possible.
Since fulltext search loads with sql 2005 (I believe), does this mean I can't do an sql server 2000 full text search on a local machine (or server) that has both of sql2000 & sql 2005 installed.
Thanks for any help on this
I have conflicting info about whether SQL 2005 Express contains full-text search and catalogueing functionality. I have read several places online where it is stated that "SQL Server 2005 Express Edition with Advanced Services" contains full-text search, but that seems to conflict with the info presented here:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
...for clarification, see my screenshot:
http://www.builderdash.com/SQL2005.png
What's the real deal, and why the conficting info?
hi,
im pretty new to sql server
I created a database with full text enabled, I created the indexes and queried the db in managment studio express and everything worked great.
when i try the same thing from within vc# it tells me that i have to enable fulltext indexing and create an index before i can use CONTAINS
what do i need to do to make my already fully functional fulltext indexes work from within vc# express 2005
thanks
I have Googled the terms 'move path change full-text index catalog' and have come up with nothing that pertains to SQL 2005...only 2000 and 7.0....
So my question remains, how the heck do you change the location of the full-text catalog in SQL 2005? Must I delete and re-create? If so is there a good article on this process? I don't want to loose data or screw anything up....I can't imagine I am the first person who wants to do his?????
Posted - 09/19/2007 : 05:19:21
I have setup full text search on a table. Everything works fine. Now I want to find documents that contain the next string: 'T-150/04'
The query will give zero results. Is there a way to find documents that contain these kind of strings? (slahses and minus)
I use the following query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"T-150/04"' )
If I do this query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"judge"' )
It will work fine. I know it has to do something with the interpunction things (slahs and minus), but I don't know what query may solve my problem.
Also a select * from containstable(jurisprudentie, text, '"T_150_04"' ) doesn't give me the proper result.
The minus sign and the slash are disturbing things. If i search for "150" or for "04" I will find the record, but I would like to speficically search for the exact string "T-150/04". Because these are references to case law issued by the court.
I hope someone can help me out.
Regards,
CBGE
Hi,I would like to have a full-text search from a sql server database in my aspx page. I have tried to do it by creating a Dataset with a DataTable where there is a query like:
SELECT title, yearFROM ProjectsWHERE title like N'%@Search%'
Problem is with the like operator - the visual studio does not reconize the @Search as a parameter, as it reconnizes it in the query below (which works fine by the way):
SELECT Projects.*, theme AS Expr1FROM ProjectsWHERE (theme = @theme)
Can anybody help me how to deal with this problem, which is the best way to have a full text search?
Cheers, ArtoV
I installed SQL Server 2005 express with advanced services which is supposed to include full-text search capability but I can't get it to work. When I try to create a full-text catalog it gives me an error because it does not think the full-text service is installed or loaded. I can't seem to find a reference to the full-text search feature to enable or install it. any ideas?
View 3 Replies View RelatedHello, I have a database that has Full Text Search it works great under Management Studio Express. I can use the CONTAINS expression no problem. Now when I try using the same database in Visual Studio 2005 the CONTAINS statement it doesn't return any values and I don't get any error messages. The way I call my database from my web.config file is as follows: <add name="MyDB" connectionString="Data Source=.FULLTEXTSEARCH;Integrated Security=True;AttachDBFilename='D:My DocumentsVisual Studio 2005WebSitesApp_DataMyDataBase.mdf';User Instance=false" providerName="System.Data.SqlClient"/> Why doesn't Full Text Search work when I use it from my web application and it does work under SQL Server Management Studio Express? Please help, Louis
View 2 Replies View RelatedHi, There have been a number of posts on querying multiple tables using full text in SQL Server. I haven't quite found the answer I'm looking for, and I'm aware that in 2005 some full text functionality changed. I have an Events table which joins to a Venue table and also a many-to-many link (via a table called EventsToArtists) to an Artists table.When a user searches for "Rolling Stones Wembley" (without the apostrophes), I want the query to look in the following columns:Event.EventNameEvent.DescriptionArtists.ArtistNameVenue.VenueNameI have done quite a bit of testing on this, and haven't yet got the desired results. Rolling Stones will be found in the Artists table, and Wembley in the Venue table. At the moment, if I search for Rolling Stones, it behaves as expected. But if search for the venue name as well I get no results. I'm pretty sure this is because EventName wouldn't contain all key words and so the record is excluded from the results. At the moment I am using INNER JOINs between the tables....perhaps I should be executing a full text search on each table's catalogue and then do a UNION to join the resultsets?Hope you can point me in the right direction.Thanks
View 5 Replies View Relatedthe sql server documentation states that the use of wildcards is allowed by placing an '*' at the end of the search term. I can get this to work OK in the SQL Server 2005 query window, heres an example
select ID, SUBSTRING(Title, 1, 100) AS Title, Implemented, Published from Table1 where contains(title,'"Therap*"') ORDER BY Title
this works OK and returns a list ot titles with the word Therapy in the title
Im trying to implelemnt this functionalty in a web app with C#. The string is passed to a stored procedure. How on earth do I pass in the quotes ??
Ive tried building the string as normal then adding single quotes on the end, so I get something like
retval = txt + "*"; //txt contains the partial word im searching for, then add the wildcard
then retval = "'" + retval + "'"; // add the single quotes
and pass txt as a string parameter to my stored procedure. It doesnt work. Can anyone tell me what im doing wrong ??
the same query works fine in the SQL query window.
Hello everybody,
I've got a little problem wich i'm trying to solve since 1-2 years and i hoped it would go away with SQL 2005 - but that wasn't the case :(.
Situation:
I've just bought a new Server containing:
SQL 2005
64 Bit Enviroment
4 GB RAM
2x AMD Opteron 2 GHz Prozeccors (Dual Core)
2x RAID Controllers (RAID 1) containing
1.1 System
1.2 Data
2.1 Transaction Logs
I've created a full-text table containing all the search terms i need to search.
Table build:
RecID - int - Primary Key
SrcID - varchar(30)
ArticleID - int - referring to an original table
SearchField - varchar(150) - Containing the search terms
timestamp - timestamp field
Fulltext index:
RecID as Primary Key
SearchField as indexed field - Wordbreaker: Neutral (containing several languages), Accent sensitivity off
Now i've got different tables imported in here resulting in a table size of ~ 13 million rows.
There is no problem with the performance on this catalog if i search a term wich isn't contained in more than 200-300 recordsets - but if i search for a term wich could occur in 200'000 upwards it gets extremely slow.
On the slow query the first records get in after no time, but until the query finished up to 60 seconds pass.
The problem is that i have to sort by a ranking value wich is stored externally - so i need all results to sort them...
current (debugging) query:
SELECT ArticleID FROM fullTextTable AS ft INNER JOIN CONTAINSTABLE(FullTextCatalog,SearchField,'"term*"') AS ftRes ON ftRes.[KEY]=ft.idEntry
Now if i check in the performance monitor:
As soon as i run the query the 'Avg. Disk Read Queue Length' counter on disk D (SQL Data Files) jumps to the top, until the query has finished.
Almost no read/write activity on C: where the Fulltext is stored...
If i rerun the query, after it finished once successfully - it takes place below 1-2 seconds, would be nice to get that result in first place :).
Does anybody know a workaround to this problem?
I'm using the Full text functionality of SQL 2005 Express Advanced and it works well.
However, the first time a query is run it take 20 seconds or so to actually return the search results. Thereafter, the search results are almost immediate.
That would be fine, except that if no further search is performed for approx 15 minutes or so, then we are back to the first scenario where it takes 20 seconds to return the results.
I assume this means that the full text catalog is not being held in cache at the server? Is there anyway to force it to do this?
My first thoughts were that Full Text within 2005 was an excellent tool, but if the first search always takes a long time then I will have to re-think as I can be sure that customers will get extremely annoyed at the response.
Do you know if there is any way around this or if there are any fixes imminent for this problem.....indeed, is it a problem or am I missing a fundamental setup issue?
What would be the implications if I created a small console application to perform a full text search once every minute at the server to keep the index cached?
many thanks
I just installed SQL Server 2005 and need to create a full-text indexing. I looked up how to do it, but the full-text indexing option is ghosted so i don't even have the option to enable it...any ideas? I tried searching for hours with no luck.
View 1 Replies View RelatedHi,
We are facing a problem while passing a string containing the "&" character into Full Text search CONTAINS predicate. The records that do have this character are not being returned by the search.
I'd like to raise two questions:
1) Is there any special way to escape this character?
2) Does FTS index it at all?
We have tried all known (to us) ways of escaping like doubling the character, using char(38), using ESCAPE etc..Nothing seem to work. Any help would be appreciated.
Thanks,
Alex
We have 2 SQL Server 2005 machines. One has the CTP version installed,the other is the RTM.When running the query below, results are returned for the CTP versionbut not the RTM version.select * from table t1inner join containstable(table, column, 'formsof(inflectional,''bücher'')') t2on t1.[id] = t2.[key]I have checked that the same data is present in the tables on bothmachines and also that the FTI has been fully populated with a statusof "idle".Please note that the word used "bücher" is a German word and shouldreturn "buch" as a match.Is this a bug with the RTM version of SQL Server 2005??
View 1 Replies View Related Hi! I am moving this from the Transact SQL forum as I am not getting a reply. Hope it's OK!
Hello: I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).
I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.
I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.
// This query //
SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')
// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>
// returns this //
A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.
Appreciate your help, Thank you.
We had a sql 2000 db that had full text implemented on it. We upgrade that server to a 2005 server and now the full text searches that once worked don't. Most do work just the ones with ; or special characters in the query string don't work.
Anyone have the same behavior and how to get it to wrok again?
thanks