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?
LVL 1
janaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You will definitely want to run the upgrade advisor in SQL 2008.  Below is a link to a step-by-step tutorial...

http://sqlmag.com/sql-server-2008/how-upgrade-sql-server-2008-sql-server-2000
janaAuthor Commented:
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.
Brian CroweDatabase AdministratorCommented:
If you're running CLR queries or full-text indexing or something like that then you might need to take a look at them but otherwise you probably won't have too many problems.  Any significant changes in T-SQL were additions (new data types, common-table-expressions, etc).  You probably won't have to change your application at all.  And yes all of your triggers, procedures, etc will come along with the data.

If possible I would recommend doing the upgrade in a staging environment prior to doing it on your production databases as you should with any significant change.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

janaAuthor Commented:
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?
Brian CroweDatabase AdministratorCommented:
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
janaAuthor Commented:
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?
Brian CroweDatabase AdministratorCommented:
select * From sys.fulltext_catalogs
janaAuthor Commented:
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?
Brian CroweDatabase AdministratorCommented:
No matter what you choose to do you should definitely do a full backup before you try anything.  I think you're safer going the restore route over the attach method.

I would certainly recommend the following
check your compatibility level.  If you want to take advantage of the new features of 2008 you will need to be at compatibility level 100
https://msdn.microsoft.com/en-us/library/bb510680.aspx
rebuild indexes
update statistics
run CHECKDB for good measure

don't forget that any sql agent jobs reside in the msdb database along with instance logins.
janaAuthor Commented:
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?
janaAuthor Commented:
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
janaAuthor Commented:
So should we check that after the upgrade and it should have "100"?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Run the Upgrade Advisor on the old databases first. Check the report. If no issues found then the migration should be simple as backup/restore or detach/attach and set the compatibility level to the correct one.
To transfer logins best thing to do is to follow Microsoft's article.

Usually hard things to migrate are DTS packages and jobs.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
janaAuthor Commented:
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).
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not really problems but you won't get the best of the new version if you don't do that.
Also upgrade your clients with the proper MSSQL ODBC and SNAC versions.
janaAuthor Commented:
Thanx!
janaAuthor Commented:
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's the high overview.
For very large databases you can replace the reindex with update statistics. It's faster and will do the purpose.

NOTE: If you're migrating MSSQL 2000 databases then the first thing to do after setting the compatibility level is to run the following command:
DBCC CHECKDB ('DatabaseName' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY

This is something new since MSSQL 2005 and so the above step enables the data purity check in the migrated database (good to control invalid data).
janaAuthor Commented:
Thanx!!!!
janaAuthor Commented:
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
janaAuthor Commented:
Thanx! We are currently working and currently testing some DB with the steps recommended.

Thanx!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.