Detaching database in 2000 > Attaching database in 2005

Gumleguf

Hi all,

The exercise I am doing is upgrading from Nav 3.70/SQL2000 -> Nav 4.03/SQL2000 -> Nav 5.01/SQL2000. All of this went good. Now I need ti finalize by migrating to SQL2005.

Can I simply detach the databse from SQL2000 and attach it to SQL2005, or is there some security settings etc. I need to do?

Someone has asked a similar question in a different forum - http://dynamicsuser.net/forums/p/19762/115251.aspx - but I can't quite find the answer to my question.

/G

jlandeen

How big is your database? If it's not too large you could simply back up from your old NAV database and into your new SQL 2005 one?

Technically you should be able to just detach and attach the database files to the new server - but there may be compatability level issues and other issues that prevent you from getting the full use of SQL 200 5 capabilities on the database.

Gumleguf

The database is around 25GB. I have actually started a backup export of the database from SQL2000, that I'm going to import into SQL2005 tomorrow. I was just trying to skip this last step, because it takes so long time.

But you are saying it's safest to take the export/import road rather than detach/attach?

dmccrae

SQL Server 2005 applies upgrade steps when attaching a SQL Server 2000 database to it. I have never seen a problem in doing this, you do not need to backup/restore either from NAV or SQL Server.

Gumleguf

Cool. I have a few more of these scenarios to go through, so I'll remember to try the detach/attach next time.

Thanks guys!

stryk

Hi!

Well, I've done that plenty of times: detach 2000 - copy/move - attach 2005: no problems, never. As soon as you attach a SQL 2000 database to a 2005 server it is converted to a SQL 2005 db; but you have to change the "Database Compatibility Level" from "80 (2000)" to "90 (2005)". This "Compatibility Level" defines how the TSQL syntax is interpreted; e.g. to use all the new DMV etc. you need to set it to "90".

The only aspect to consider might be: which is faster? Making a SQL backup and restore, or detach/attach? This actually depends on the size of the database files, the space used and the capabilities of the disk-subsystem ...

When it is about "security", of course, you have to set up the Logins and Users in the new server, too. But this script/process will help you: http://support.microsoft.com/kb/246133

Kind regards,
Jörg

kine

And look there: http://dynamicsuser.net/blogs/kine/arch ... -2005.aspx

I described one thing you need to take into account when moving DB from MS SQL 2000 to higher version...

Gumleguf

Thanks guys. Great stuff.