| Author |
Topic |
|
kkm
Starting Member
15 Posts |
 Posted - 2008-09-11 : 09:02:45
|
| We have changed servers from old one to new one with a better configuration. I have installed SQL server 2005 standard edition in the new server. In the old server I have SQL server 2000 having multiple databases. Now I want to move all the database from old 2000 to new 2005. I need help in the following aspects.I have created the back ups fo all the databases including master, msdb & model. I am trying to restore all the database.the questions I have are as follows:1 When I try to restore master database I am getting an error that server must be running in single user mode. can we not restore with out changing to single user mode and if not can you please let me know how to change to single user mode?.2 After restoring all the databases including master , model and msdb do you think all the dts packages will restored automatically. If not can you please let me know what else I need to do.3 Can you also please let me know how to restore the logins including the passwords4 I am also expecting different collations . How to configure the Collation in 2005 to be same as in 2000.5 Is ther any other issues which needs to be resolved for the successful move from 2000-2005I greatly appreciate help on all the above issues.Thank you in advance kkm |
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-09-11 : 15:06:33
|
| I don't know why you r restoring system db's????After you restored your db's,you have to change the compatibility to 90you have to restore the dts packagesyou can use sp_help_revlogins,it will copy everything.Guys,Correct me if i was wrong!!!!!!! |
 |
|
|
kkm
Starting Member
15 Posts |
Posted - 2008-09-12 : 06:48:00
|
| Thank you for the advice1 Do you think it is not required to restore System DB's?2 What do you mean by changing compatibility to 90? |
|
|
|
lepeniotis
Yak Posting Veteran
75 Posts |
Posted - 2008-09-12 : 08:31:09
|
| Hi, I don't think that restoring the master DB form 2000 to 2005 will give you any profit. What I did and worked was to create the dbs in 2005 with the same names and properties and restore the 2000 dbs on the dbs that I created on 2005. It worked fine for me. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-12 : 08:58:07
|
| 1) use this to transfer logins(Which resolves master database)http://support.microsoft.com/kb/246133Make sure you execute sp_help_revlogin 2000 to 20052)Make sure your DTS/SSIS packages are working in 20053)Transfer all jobs to SQL 2005 with Transfer Job task4)Change Compatibility to 90 in Database Properties5)Rebuild indexes after you upgrade. |
|
|
|
kkm
Starting Member
15 Posts |
Posted - 2008-09-12 : 10:24:32
|
| 1 I did not find compatibility item under database rpoperties can you please let me know how to change this. 2 If we are restoring the databases do you think rebuilding indexex is required.3 How do we use transfer job task |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-12 : 10:29:33
|
quote:
Originally posted by kkm
1 I did not find compatibility item under database rpoperties can you please let me know how to change this. Database-Properties-Options2 If we are restoring the databases do you think rebuilding indexex is required.Yes3 How do we use transfer job task
In SSIS-ControlFlowtask-Transfer Job Task |
|
|
|
kkm
Starting Member
15 Posts |
Posted - 2008-09-12 : 11:08:06
|
| Thank you for the info . However I did not find compatibilty under options tab for the selected database. have I done wny thing wrong in the installation here. |
|
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-09-12 : 14:57:19
|
| Right click on Database -->Property -> In propertys you will find options,click on options,you will find three options on the top of the page1.collation2.RecoveryMode3.Compatibility Levelclick the compatibility options you will find SQL Server 2005(90)Click OkThats it. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-14 : 00:18:43
|
quote:
Originally posted by kkm
Thank you for the info . However I did not find compatibilty under options tab for the selected database. have I done wny thing wrong in the installation here.
Thats because you haven't restored in SQL 2005. |
|
|
|
kkm
Starting Member
15 Posts |
Posted - 2008-10-02 : 10:50:28
|
| Hi I have migrated the databases from 2000 to 2005 I created backups and restored it it in new server 2005 and also trasnsfered the logins using the script.The collation remains same in both 2000 and 2005 servers which is : SQL_Latin1_General_CP1_Cl_AS and I changed the Compatibility level to: SQL Server 2005(90) After this, I tested my front end Dashboard Application with the connection to new database server and the database and I am getting errors in my queries for example: a simple query like--SELECT Distinct Year, Year FROM inventory_historyorder by year descWhen I execute the same query in SQL server M studio it works fine.After this, I changed the Compatibility level in 2005 to: SQL Server 2000(80) . My front end dashboard application works fine woth out any errors. Can I get help please to resolve this. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-02 : 11:26:56
|
| There are some queries that won't work with 90 Compatibility .See Difference between 80 and 90 compatibility in SQL Server. |
|
|
|
kkm
Starting Member
15 Posts |
Posted - 2008-10-02 : 11:34:25
|
| Is it like we need to change the queries. But the query qorks in SQL server Management studio but the same does not wok in the front end!! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-02 : 12:08:12
|
quote:
Originally posted by kkm
Is it like we need to change the queries. But the query qorks in SQL server Management studio but the same does not wok in the front end!!
Since you changed it to 90, it will work on SSMS. Your apps is still using 80 compatibility query. |
|
|
|
|
Move databses from 2000-2005