Recovery :: Restoring 2005 Database With Full Text Catalogs To 2012?
Nov 15, 2015
We 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 ?
I have a handful of databases that are enabled for Full-Text search. After investigating some recent performance issues, I discovered the FullText Catalogs needed to be reorganized. This is a task I knew I wanted to automate, without having to hard-code db names or catalog names. My first thought was to use sp_executesql with dynamic tsql strings. I was quite disappointed to realize that I couldn't use fully qualified names to run either of these commands:
ALTER FULLTEXT CATALOG [DBName].[SchemaName].[CatalogName] REORGANIZE ALTER FULLTEXT CATALOG [DBName]..[CatalogName] REORGANIZE
My next thought was to create a stored proc on each user db that would do the re-orgs. Then I could have a sql job iterate through the db's and run the sp on each db. Thinking...Hmm...That's do-able, but I don't like it. Add a new db to the server, and I have to remember to create the sp. Relying on my memory to do something isn't always a good idea. Plus, if I have to fix/edit/enhance the sp, I get the pleasure of doing it multiple times on multiple servers. Too much work.
I came up with some code that would dynamically reorganize all the catalogs, but I had to run it while connected to a specific db. How do I run the code while connected to [master], but in the context of a different db? The undocumented proc [sp_MSforeachdb] came to mind. I'd never used it, and was reluctant to do so after reading about other dba's experiences with it. So I came up with my own derivitive, just for this one purpose. The code is below.
CREATE PROCEDURE dba.ReorganizeFullTextCatalogs AS /* Purpose: Reorganizes the FullText Catalogs (as needed) on all user databases.
Inputs: None
History: 02/25/2014DMasonCreated */ --This is the tsql statement that get executed on each db. DECLARE @InnerSql NVARCHAR(MAX) = 'DECLARE @Tsql NVARCHAR(MAX)
SQL Server 2000 is pretty well documented with the limit of 256 (see http://msdn2.microsoft.com/en-us/library/aa214780(SQL.80).aspx) but I can find no documentation anywhere that discusses the limit on SQL Server 2005.
I've searched the forum and saw that this question has been asked several times. However, none of the responses point to any documentation about this limit. We've got different groups bickering over this ("There is no limit!" "Yes there is!" "No there isn't!") and it would help to have a link on the MSDN or a response from Microsoft to this question to put this to rest once and for all. Also, if there is a limit, is that per Sql Server instance or per physical server?
Secondly, we currently have over 400 catalogs. We've noticed it takes 4-5 minutes for new data to show up in search results. Does the number of catalogs affect performance, or is it the amount of data, irrespective of catalog count?
looking for some views about where to place these catalogs. The manual says they work more effiecntly on a different drive to the database, which is understandable.
I'm in the processes of designing a new database application which is to be avaiable via the internet, and extranet app. But as I'm planning to host the app and the main database on a shared server but to have the catalogs on another server held in house. The app will have search facilities as its main use. They will obviously be some performace issues, but is this a good idea?
When I create a full text catalog via sql code, does the account the sql server is running under need any special permissions, since some files are created?
Or is it just the same as running any other sql code?
I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message
Msg 7610, Level 16, State 1, Line 2 Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid. Msg 3156, Level 16, State 50, Line 2 File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 2 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
[code]....
I went as far as giving the folder full access to everyone temporarily and received the same error.
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
When restoring a database that contained a full text catalog to another server I noticed that the full text catalogs were not restored. However, the system tables still had the information on these catalogs. As such, I couldn't delete the catalogs (because they didn't exist).
In the end, I deleted the relevant lines from the system table sysfulltextcatalogs and sysobjects and then built a new set of catalogs. Obviously this isn't ideal.
I am restoring databases to a backup/standby server on a regular basis and was wondering if there are any official/documented methods of restoring full text catalogs to another server. (i.e. without having to mess around with the system tables). There doesn't seem to be much in BOL.
I am having a problem restoring to a new or different database than the backup file being restored was created from. I understand the Move commands, and have figured out how to get this to work by mapping the data and log files from the backup file to the files defined for the restore target database.
The problem is with the full text catalog that is part of the backup.
Here's the T-SQL I'm executing, and the error I receive:
T-SQL: RESTORE DATABASE New_DB FROM DISK = 'C:ackupOld_DB.bak' WITH RECOVERY, REPLACE, MOVE 'Old_DB' TO 'C:SQLDataNew_DB.mdf', MOVE 'Old_DB_log' TO 'C:SQLDataNew_DB_log.ldf'
Error: Msg 1834, Level 16, State 1, Line 1 The file 'C:SQLDataFTDataftKeyWords0007' cannot be overwritten. It is being used by database 'Old_DB'. Msg 3156, Level 16, State 4, Line 1 File 'sysft_ftKeyWords' cannot be restored to 'C:SQLDataFTDataftKeyWords0007'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
Not sure what to do about this, any help would be greatly appreciated!
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.
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?
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
I have two instances (Source and Destination) on the same server, i did a full backup of a database on the source server, i did a restore on the destination using the NORECOVERY mode, now the database on the destination as green arrow saying "restoring". From the source database i set up the mirroring, i'm using port 5022 on source and 5023 on destination, everything seems to connect fine but when i start mirroring i get the error message "Database mirroring cannot be enabled because the database is not full recovery error: 1465" but when i look at the property of the db on the source the recovery model shows full as so the destination?
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
'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()
We had a siutation last night in our production environment that forced us to revert back to an earlier version of the database (before a major code rollout that failed). After restoring the days full backup (with NORECOVERY), and then restoring a DIFF backup (FULL RECOVERY and had checked Preserve Replication Settings)...the transaction replication failed.
Message #1 The replication agent has been successfully started. See the Replication Monitor for more information.
Message #2 2011-03-04 15:07:17.566 Copyright (c) 2008 Microsoft Corporation 2011-03-04 15:07:17.566 Microsoft SQL Server Replication Agent: logread 2011-03-04 15:07:17.566 2011-03-04 15:07:17.566 The timestamps prepended to the output lines are expressed in terms of UTC time. 2011-03-04 15:07:17.566 User-specified agent parameter values:
[code]....
I've tried reinitializing the publication/subscription and while that took brand new snapshots and copied it over to the replicated data server, it did not fix the problem.I read from a different post that I could try running "sp_replrestart" but that ran for about a half an hour and didn't appear to do anything but fill up our log files...did I not wait long enough?
The only thing I know to do at this point is to drop the publication on the production server and rebuild it completely (and with all the tables we're replicating that would take quite a bit of time.
Full Text Searches are working on my test server, but not on my production server. My test scenario is as follows:
CREATE FULLTEXT CATALOG FTC_Test AS DEFAULT AUTHORIZATION dbo
CREATE FULLTEXT INDEX ON guest.FtsTest(FullName) KEY INDEX PK_FtsTest ON FTC_TestI wait briefly and then check to see if the index has been populated: SELECT * FROM sys.fulltext_indexescrawl_end_date is not null,
So I'm assuming I don't have to wait anymore before I try some FTS searches. Right? I can't get any queries to return anything, though.
The following tells me the full text item count for the table is zero:
DECLARE @TableId INT SELECT @TableId = id FROM sys.sysobjects WHERE [Name] = 'FtsTest' SELECT OBJECTPROPERTYEX(@TableId, 'TableFulltextItemCount') AS TableFulltextItemCount
As mentioned, the full text search works on my test server. Both of them are SQL 20012 SP1 (11.0.3000) x64 running on WinServer 2008 R2 SP1.
I'm using the Full Text Search with the neutral language in the indexed columns. I have no stoplists so everything counts. We changed to neutral language because our customers don't have the need for language specific criteria. They just want raw text search.Everything was working great but then they found a term that they can't search and I can't figure out what's wrong. It comes down to this:
SELECT * FROM sys.dm_fts_parser('"1.2.3.4"', 1033, NULL, 0); SELECT * FROM sys.dm_fts_parser('"1.2.3.4"', 0, NULL, 0);
In the neutral language, the parser will return "3.4", differently than the English language parsing.In the 1033 language, the parser will break all the numbers into words and the result will show correctly.I really don't want to change the language from neutral because it's working for every other query.
I am new to Full-Text Searches and FileTables. I am working on a resume search system. I created a FileTable and the Share which contains about 51,000 resumes in Word (DOC/DOCX), PDF and TXT formats that were copied over from our main file server. I followed the instructions on adding the Microsoft Filter Pack 2.0, and all searchable file types are listed in sys.fulltext.document_types.
I have a program that searches resumes by First and Last Name and/or E-mail Address. For a while, we thought it was working fine because we were getting hits, but then we noticed some Names and E-mail Addresses we new existed were not showing up in my CONTAINS queries -- but we verified the documents do exist and the Names exist in the File Name and inside the document along with the E-Mail address.
One example I'm working on now cannot find the First/Last Name with CONTAINS(name,"lastname") in a Word DOC file, but I can find the document using LEFT(name,5) = 'Smith'. It's a Word DOC file. For testing purposes, I opened the DOC file in Word, then used Save As to create a TXT and DOCX file within the same folder. Now, both of those are showing up in the CONTAINS but not the original DOC file.
So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).
I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!
I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:
keyword: 0xFF display_term: END OF FILE column_id: 2 document_count: 40
So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?
I'm standardizing street addresses and i think full text search, maybe, work fine with my problem. My problem is that I have 2 millon of errors and using "LIKE" dont work correctly. This is what i have now:
UPDATE addresses SET standarAddress = REPLACE (addresses.streetAddress, errors.streetAddress, corrects.streetAddress) FROM corrects INNER JOIN errors ON corrects.id = errors.idCorrects
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername, from CustomerTable where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430 ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
We're having a bizarre issue with installing our SQL 2012 cluster. On the stand-alone instances, we're able to choose Replication and Data Quality Services from the Database Engine Services without choosing Full-Tex and Semantic Extractions for Search. But when installing the cluster, it won't let us choose the other two without it. In fact, if we click on either Replication or Data Quality, it auto-checks ALL of the features.
My coworker discovered this problem and I was able to replicate it. We're using the SP1 install msi.
Why this is happening with a cluster install but not a stand-alone install?
My install appears to be hanging at the as_cluster_ip_address_cluster_config_Cpu64 part and I'm wondering if it's because it's trying to install Full Text.
So the cluster install forced us to install Full-Text on our cluster (which we don't want to use, but I guess we don't have a choice about installing it). Now I'm going through to update the services from Manual to Automatic (on the appropriate nodes) and I see SQL Full-text Filter Daemon Launcher running.
What is the harm in turning it off if we're not using Full-Text capabilities?
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
In SQL Server 2005, the SQL Server full text engine (i.e., Microsoft Full Text Filter Daemon Launcher windows service) was installed as a Generic Service cluster resource in a Windows clustered environment.
However, in SQL Server 2012, the full text engine is not appearing as a "resource" within "Failover Cluster Manager" after installation. Do we need to manually configure the full text engine as a Failover Cluster Manager "resource" for SQL 2012? Or is this not necessary? If it needs to be configured, how do you go about doing it (i.e., what resource dependencies would you setup, etc.)
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
