There are couple of things to consider :
backup and restore is the safest option that you can use for migration from SQL 2005 to 2012.
Refer here for more details of what you are experiencing.
Full text has undergone major chance in 2008 and up.
from BOL,
Attaching a SQL Server 2005 Database to SQL Server 2012
In SQL Server 2008 and later versions, a full-text catalog is a logical concept that refers to a group of full-text indexes. The full-text catalog is a virtual object that does not belong to any filegroup. However, when you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2012 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005.
The state of each attached full-text catalog on SQL Server 2012 is the same as when the database was detached from SQL Server 2005. If any full-text index population was suspended by the detach operation, the population is resumed on SQL Server 2012, and the full-text index becomes available for full-text search.
If SQL Server 2012 cannot find a full-text catalog file or if the full-text file was moved during the attach operation without specifying a new location, the behavior depends on the selected full-text upgrade option. If the full-text upgrade option is Import or Rebuild, the attached full-text catalog is rebuilt. If the full-text upgrade option is Reset, the attached full-text catalog is reset.
EDIT:
You can attach the detached database along with the fulltext catalog using Create database ... FOR ATTACH.
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON
(FILENAME = 'D:\...\Data\AdventureWorks_Data.mdf'),
(FILENAME = 'D:\...\log\AdventureWorks_log.ldf'),
(FILENAME = 'D:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO
I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...
Your test is flawed.
What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.
And we can see that the buffer pool was not totally blown away by the
detach/attach. Seems like my buddy was wrong. Does anyone disagree or
have a better argument?
Yes. You're interpreting physical reads 0
as meaning there were not any physical reads
Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads
0, read-ahead reads 768, lob logical reads 94, lob physical reads 4,
lob read-ahead reads 24.
As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.
When SQL Server performs a sequential scan of a large table, the
storage engine initiates the read ahead mechanism to ensure that pages
are in memory and ready to scan before they are needed by the query
processor. The read ahead mechanism tries to stay 500 pages ahead of
the scan.
None of the pages required to satisfy your query were in memory until read-ahead put them there.
As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.
Best Answer
Since you mentioned earlier that your DB gets restored daily and it has different
filegroups
, it seems to me the case of piecemeal restore. You need to check the script used for restore and make sure it is recovering all thefilegroups
present in the DB backup.It looks like currently the script is only recovering
primary filegroup
and skipping the others which is causing the DB to recover but without theskipped filegroups
. So you can query all your data which is present inPRIMARY filegroup
but not the skipped ones. Just recover all thefilegroups
using the example given in below link and you should be fine.http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/sql-server-filegroup-piecemeal-restores-1/