Proper way transfer db from mssql 2000, 2008 to 2008

jana
jana used Ask the Experts™
on
hi we various databses in sql 2000 & 2005 and need to move them to 2008.  what are optimal steps to follow?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005
Commented:
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

Author

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 Administrator
Top Expert 2005
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 Administrator
Top Expert 2005

Commented:
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

Author

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 Administrator
Top Expert 2005
Commented:
select * From sys.fulltext_catalogs

Author

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 Administrator
Top Expert 2005
Commented:
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.

Author

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?

Author

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

Author

Commented:
So should we check that after the upgrade and it should have "100"?
IT Engineer
Distinguished Expert 2017
Commented:
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.

Author

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ãoIT Engineer
Distinguished Expert 2017
Commented:
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.

Author

Commented:
Thanx!

Author

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ãoIT Engineer
Distinguished Expert 2017
Commented:
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).

Author

Commented:
Thanx!!!!

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

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

Thanx!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial