moving databases from a SQL 2000 to 2005 server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SQL Programmer - 23 Jul 2008 18:37 GMT

We have set up a test server that has SQL 2005 on it. If we restore backups
of our SQL 2000 databases from our SQL 2000 server into SQL 2005 on our test
server, will this upgrade the databases to 2005? Or, do we need to go
through an upgrade "process"?

We just want to find a simple means of upgrading the databases after moving
the backups to this test environment.

SQL Programmer (it's just a name)

Reply to this Message

Roy Harvey (SQL Server MVP) - 23 Jul 2008 18:58 GMT

The "upgrade" is automatic (and one-way). You will want to double
check the comparability level though. 80 is 2000 comparability level,
90 is 2005. See the documentation on sp_dbcmptlevel for more on
comparability levels.

Roy Harvey
Beacon Falls, CT

>We have set up a test server that has SQL 2005 on it. If we restore backups
>of our SQL 2000 databases from our SQL 2000 server into SQL 2005 on our test
[quoted text clipped - 5 lines]
>
>SQL Programmer (it's just a name)

Reply to this Message

SQL Programmer - 23 Jul 2008 19:10 GMT

Thanks, Roy!!!

SQL Programmer (it's just a name)

> The "upgrade" is automatic (and one-way). You will want to double
> check the comparability level though. 80 is 2000 comparability level,
[quoted text clipped - 13 lines]
> >
> >SQL Programmer (it's just a name)

Reply to this Message

Plamen Ratchev - 23 Jul 2008 19:04 GMT

You just need to restore the databases on SQL Server 2005, then check the
compatibility level is 90. A good idea is to update statistics after that.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

SQL Programmer - 23 Jul 2008 19:09 GMT

Thanks, Plamen!!!

SQL Programmer (it's just a name)

> You just need to restore the databases on SQL Server 2005, then check the
> compatibility level is 90. A good idea is to update statistics after that.
[quoted text clipped - 3 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Roy Harvey (SQL Server MVP) - 23 Jul 2008 19:18 GMT

>A good idea is to update statistics after that.

Thanks Plamen for catching that important point I missed.

Roy

Reply to this Message

TheSQLGuru - 23 Jul 2008 22:27 GMT

I think updating statistics (with FULL SCAN option) actually falls under the
category of "mandatory" after moving a db from 2000 to 2005+. :-)

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

Reply to this Message

Roy Harvey (SQL Server MVP) - 23 Jul 2008 23:05 GMT

>I think updating statistics (with FULL SCAN option) actually falls under the
>category of "mandatory" after moving a db from 2000 to 2005+. :-)

Yes, of course. That is exactly why I made a point of thanking Plamen
for correcting my omission.

Roy Harvey
Beacon Falls, CT

Reply to this Message

Uri Dimant - 24 Jul 2008 08:16 GMT

Hi
Among others advises , I'd suggest to download MS tooll called Upgrade
Advisor to find a potential syntax errors and etc that are not supported in
SQL Server 2005.

> We have set up a test server that has SQL 2005 on it. If we restore
> backups
[quoted text clipped - 8 lines]
>
> SQL Programmer (it's just a name)

Reply to this Message