Link to home
Start Free TrialLog in
Avatar of jana
janaFlag for United States of America

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
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Ok will go over your link.

Questions:
In your experience, how "perfect", if we can "perfect", is that process?
Do the new Db under 2008 has all its contents such as triggers, store procedures, etc?
When upgrading, do we have to also modify our application accessing the database?
After upgrading, should we run tools like rebuild any indexes or any other tools like CHECKDB?


Please advice.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

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?
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
Avatar of jana

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

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:

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

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?
Avatar of jana

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
Avatar of jana

ASKER

So should we check that after the upgrade and it should have "100"?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

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).
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Thanx!
Avatar of jana

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Thanx!!!!
Avatar of jana

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?
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.
Avatar of jana

ASKER

Thanx! We are currently working and currently testing some DB with the steps recommended.

Thanx!