jana
asked on
Proper way transfer db from mssql 2000, 2008 to 2008
hi we various databses in sql 2000 & 2005 and need to move them to 2008. what are optimal steps to follow?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What you mean by "CLR queries" and "full-text indexing" (we do queries and some database management, no expert) .
Also why not just Unattached from 2005 and Attached 2008? What is you opinion?
Also why not just Unattached from 2005 and Attached 2008? What is you opinion?
If you don't know what they are then you're probably not using them.
https://msdn.microsoft.com/en-us/library/ms254498%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396
https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
For 2005 -> 2008 you're probably ok just using the detach/attach method but for 2000-> 2008 I would definitely use the upgrade option
https://msdn.microsoft.com/en-us/library/ms254498%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396
https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
For 2005 -> 2008 you're probably ok just using the detach/attach method but for 2000-> 2008 I would definitely use the upgrade option
ASKER
Yes, your are correct; "CLR queries" are not being used (just read your link). We have no CLR hosted in Microsoft SQL Server.
As for "Full-Text Indexing", we don't think so either, but we do remember when we installed the sql server there was an option to activated (maybe). Is there a way to know if our SQL uses Full-Text Indexing?
As for detach/attach, should we instead make a backup in our previous SQL and restore in our new SQL? Would that be a safer method? What do you think?
Finally, after the upgrade, is it necessary, even we we do do a attach/detach or backup/restore, should we run tools like rebuild any indexes or any other tools like CHECKDB?
As for "Full-Text Indexing", we don't think so either, but we do remember when we installed the sql server there was an option to activated (maybe). Is there a way to know if our SQL uses Full-Text Indexing?
As for detach/attach, should we instead make a backup in our previous SQL and restore in our new SQL? Would that be a safer method? What do you think?
Finally, after the upgrade, is it necessary, even we we do do a attach/detach or backup/restore, should we run tools like rebuild any indexes or any other tools like CHECKDB?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just ran your "select * From sys.fulltext_catalogs" and it came out empty; this means our SQL don't use Full-Text Indexing?
Lastly, picking your brain on this:
Lastly, picking your brain on this:
Should we stick Detach/Attach or make a backup in our previous SQL and restore in our new SQL?
After having the DB in SQL 2008, are ther any tools that we should run like like rebuild any indexes or any other tools like CHECKDB?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Checked your link; the link is to "SET" (modify the db)
How to do see the existing level?
Is this done prior upgrading or after?
How to do see the existing level?
Is this done prior upgrading or after?
ASKER
Ok got it:
Select name, compatibility_level , version_name =
CASE compatibility_level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END
from sys.databases
Select name, compatibility_level , version_name =
CASE compatibility_level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END
from sys.databases
ASKER
So should we check that after the upgrade and it should have "100"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, we will run Upgrade Advisor on all old DB.
Thanx for clearing up the "compatibility level" point. So we HAVE to be set the "level": after the backup/restore or detach/attach (otherwise, there can be problem if not set).
Thanx for clearing up the "compatibility level" point. So we HAVE to be set the "level": after the backup/restore or detach/attach (otherwise, there can be problem if not set).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx!
ASKER
So in conclusion, run Upgrade Advisor, if all ok then backup old db then restore in sql2008, set compatibility level on all imported DB, run Rebuilding indexes and db check tools, transfer all logins, make sure all client odbc & snac version are set.
We know that the actual steps are more details, specific to each of their options, but as a summary, is the above correct? Are we missing anything?
We know that the actual steps are more details, specific to each of their options, but as a summary, is the above correct? Are we missing anything?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx!!!!
ASKER
Note, just reread your entry. We do have "very large databases", from 15gb to 40gb (i think).
With this size, we ok with what has been said here by you guys? Do we have to change anything on the overviews steps discussed here?
With this size, we ok with what has been said here by you guys? Do we have to change anything on the overviews steps discussed here?
The recommend by Microsoft is to run Update Statistics and not Reindex. But personally I would prefer to run Reindex since it's more complete command but the reverse side is takes longer and that's why I told you to run Update Statistics in VLDB.
But you should be ok by running only Update Statistics and schedule Reindex to run later at night.
But you should be ok by running only Update Statistics and schedule Reindex to run later at night.
ASKER
Thanx! We are currently working and currently testing some DB with the steps recommended.
Thanx!
Thanx!
ASKER
Questions:
Please advice.