This weekend I tried to upgrade a SQL 2000 db to SQL 2008. I tried using
the db copy wizard. It failed with a very uninformative error message.

So the way I upgraded was to backup the SQL 2000 db, restore it in SQL 2008
and then went into properties/options and switch the compatibility level to
sql server 2008 (100). Is this all I had to do? Is my db now "upgraded"?

Andre

Andre (no (AT) spam (DOT) com) writes:
Yes. Well, you may need to carry over logins from the old server. You
may also need to remap database users to the logins. But that has nothing
to do with the upgrade per se. This is the same procedure as always when
you copy a database from one server to another.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks. So logins/users aside, does SQL 2008 not need to "upgrade" the
indexes or optimize anything else in the db? I just flip the compatibility
and it's magically upgraded from 2000 to 2008? Seems awfully easy, and I
guess I was expecting it to "do" something.

Andre

Andre (no (AT) spam (DOT) com) writes:
The upgrade was when you restored the database. You may have noticed that
it took a longer time than a usual restore.

But when I think of it, you need to run sp_updatestats, as the statistics
were invalidated when you upgraded the database.

The compatibility level is just a flag that controls how queries are
interpreted and which can be flipped forth and back as you please.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx