October 1, 2007 at 8:00 am
My question is how do I get them and my system to the SQL 2005 instance (90)? I have some databases in 90 that I wish to restore to my system but cannot. I know this must be easy, but I am a novice at this and it is beyond me for now. Thank you.
October 1, 2007 at 8:21 am
I assume you are trying to migrate your 80 databases? When you say you cannot restore them, do you mean you are getting an error message? If so, what is that error message?
You should be able to restore 2000 backups to 2005 (note that SQL Server will keep these databases in compatibility mode 80 by default).
Regards,
Andras
October 1, 2007 at 8:38 am
Thanks Andras. I am OK with the 80 databases. It's the 90 databases I cannot restore to my 80 instance. What I want to do is migrate every thing to 90. I just need an idea of where to start to do this. Once again, thanks.
October 1, 2007 at 8:44 am
winnri (10/1/2007)
Thanks Andras. I am OK with the 80 databases. It's the 90 databases I cannot restore to my 80 instance. What I want to do is migrate every thing to 90. I just need an idea of where to start to do this. Once again, thanks.
If you want to migrate your 90 databases to 80, then I only see the painful way of doing it (i.e. scripting all the objects manually in 2000 syntax, and migrating the data. You should consider either SSIS or third party tools. The underlying system tables have changed quite a lot in 2005, so you will not be able to attach/restore a 2005 (90) database to 2000.
Andras
October 1, 2007 at 11:51 am
What I want to do is setup a 90 (instance) so that I can restore the 90 databases. I can migrate my 80 databases to it if I have to but I more concerned with the 90 databases. I work with SAP BusinessOne and we have clients on both. We have separate servers in the office for them. What I am trying to do is setup my server to handle the 90 databases. I upgraded from SQL 2000 sp4 to SQL 2005, but the databases stayed at 80. Hope this explains what I'm trying to do. Thank you for all your help. I really appreciate it.
October 1, 2007 at 6:32 pm
Databases upgraded from SQL Server 2000 to SQL Server 2005 will remain in SQL 2000 compatibility mode (mode 80). To switch the compatibility mode to SQL 2005 (level 90), use sp_dbcmptlevel.
EXEC sp_dbcmptlevel @dbname='Your database name', @new_cmptlevel=90
-Eddie
Eddie Wuerch
MCM: SQL
October 2, 2007 at 7:27 am
Thank you. that helps with the databases, but as you can tell, I am relatively new to this and know enough to be dangerous but am not competent. However, that being said, my ultimate objective is to get my database engine from 8.0.2039 to 9.0.1399. Is their any way to painlessly upgrade the database engine? Sorry for being a bother about this.
October 2, 2007 at 10:49 am
Not sure if you have you answer yet; But from readin the thread it sounds like this is you problem:
If you have a 2000 database you cannot restore a 2005 database into it. Change the compat. mode of the 2000 DB to 2005 then restore the backup. Once restored, change the compat mode to 2005.
Sounds like you may have done something like: Created a 2005 Database and then tried to restore your 2002 database into it.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
October 2, 2007 at 11:04 am
Thanks for your reply.
What I did was Upgrade my SQL 2000 server to SQL 2005 expecting that the database engine would upgrade form 80 to 90 compatibility mode. I have no trouble on the other servers restoring an 80 database to an SQL 2005 server with compatibility mode 90.
As you can guess, we have several servers. The one that I am trying to change from compatibility mode 80 to 90 is on my laptop. I think that maybe I should have tried a side by side installation/upgrade. Unfortunately, I didn't and now I fear the time to do so is past. I really don't care about the 80 databases, but would like to change my database engine to be able to use the 90 databases.
Rick Winn
October 2, 2007 at 11:11 am
What do you mean by "the database engine" being 80 compatability? It is only databases that have compatability levels. If you mean SQL Server itself, issue xp_msver in a query editor window to verify your versioning.
October 2, 2007 at 11:16 am
winnri -- are you still having the problem? What i was trying explain is that, if you create a 2005 database named XYZ as 2005, and then attempt to restore a 2000 database to it, the restore will fail. If you start with no database and restore a 2000 data it will work. It sounded like that may have been you issue. Hope this helps.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
October 2, 2007 at 12:14 pm
Thanks, I'll try it.
Rick
October 2, 2007 at 1:23 pm
My server version is still 2000.080.2039.00 when I did the upgrade I thought it would go to 2005.090.1399.00 like my other server. However, it did not. With Microsoft SQL Server Management Studio I can see and work on both, but it looks like the Upgrade was only partially successful, in that it did not really upgrade to 2005. It just installed the Management Studio and removed Enterprize Manager. Is there anything I can do short of uninstalling SQL Server and then Reinstalling SQL 2005 server and then reinstalling my SAP BusinessOne software?
October 2, 2007 at 1:30 pm
SQL 2000 and 2005 can run side by side. And Management Studio will work on a 2000 Database server, meaning that you can use it. When you installed 2005 did you by chance install 2005 as an instance?
Check how may instances of SQL you have running by:
START / Programs / SQL 2005 / COnfiguration Tools / Configuration Manager
I believe that is show ssomething like SQL Server(MSSQLSERVER) for the default instance and SQL Server(????) for a named instance.
Now with that being said, I'm not sure if 2000 has to be the default instance.
Hope it helps.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
October 3, 2007 at 7:57 am
Thanks. I looked at my start menu and MS SQL Server still has everything. MS SQL Server 2005 only has Analysis Services, Configuration Tools, Documentation and Tutorials, Performance Tools and SQL Server Management Studio.
It looks like the Upgrade did not upgrade the server itself. So, I'll try running the upgrade again. I must have missed something. Sorry for bothering everyone.
Rick Winn
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply