none
Migrating from SQL SERVER 2000 to SQL SERVER 2008 R2

    Pregunta

  • Hi guys, I need to migrate two data bases which are in 2000 to SQL SERVER 2008 R2, What are the steps I need to take care of. Can I directly migrate from 2000 to R2?Please guide me on this.
    miércoles, 15 de junio de 2011 0:07

Respuestas

  • Yes, you should be able to make a backup of SQL 2000 database and restore it and unlikely you get any problems. You may need to test your SPs and functions after migration, though. Also, I suggest to use compatibility mode 100 (you will change it from the properties screen once you done).

    You will not be able to do the opposite process (downsize the DB once it's 'upsized' - you may want to keep this in mind).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marcado como respuesta Sqldbar2 miércoles, 15 de junio de 2011 5:48
    miércoles, 15 de junio de 2011 3:29

Todas las respuestas

  • Any thoughts on this?

    miércoles, 15 de junio de 2011 1:11
  • yes you can if you have SQL 2000 SP4, you can start from SQl Server Upgrade Advisor: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=f5a6c5e9-4cd9-4e42-a21c-7291e7f0f852&displaylang=en 


    This posting is provided "AS IS" with no warranties, and confers no rights.
    miércoles, 15 de junio de 2011 1:53
  • I don't need to upgrade entire sql server instance I just need to migrate two database on to sql server 2008 R2. Would that be fine If I just take the back up from 2000 and resdtore it on 2008 r2
    miércoles, 15 de junio de 2011 3:15
  • It should be OK. You will probably need to also change compatibility mode of the database to 100 once you completed the restoration.

    Note, that once done, you will not be able to restore them back to SQL 2000 by any other means than scripts.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    miércoles, 15 de junio de 2011 3:21
  • Thanks Naomi, So would you suggest me to script out the entire database and its objects and save them.
    miércoles, 15 de junio de 2011 3:24
  • So I can take the backup and restore it on 2008 R2 directly. Hope I will get no problems.
    miércoles, 15 de junio de 2011 3:25
  • Yes, you should be able to make a backup of SQL 2000 database and restore it and unlikely you get any problems. You may need to test your SPs and functions after migration, though. Also, I suggest to use compatibility mode 100 (you will change it from the properties screen once you done).

    You will not be able to do the opposite process (downsize the DB once it's 'upsized' - you may want to keep this in mind).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marcado como respuesta Sqldbar2 miércoles, 15 de junio de 2011 5:48
    miércoles, 15 de junio de 2011 3:29
  • So taking the scripts and saving them would help me to go back if required. or is there any other way? Se.orry to ask you these many times as this is my first time I'm doing this on
    miércoles, 15 de junio de 2011 4:01
  • Please go through below link, you can find many more Threads on migration

    http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/threads


    Thanks & Regards Prasad DVR
    miércoles, 15 de junio de 2011 4:14
  • Yes, when you'd like to move DB back, you can script it in SQL 2008 targeting SQL 2000. If the database is not big, you may even want to script data.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    miércoles, 15 de junio de 2011 12:27
  • Hi,

            I having issue with Compatibility level in SQL SERVER 2008 R2

        my database currently in sql server 2008r2, while running some Stored Procedure  i am getting the following  error message  --

       "The query uses non-ANSI outer join operators (“*=” or “=*”).
         To run this query without modification, please set the compatibility level for current database to 80,
         using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to
         rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions
         of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes."

               My full database is now in SQL SERVER 2008 R2 &  i have more than 200 stored procedure.

                   Please suggest me to solve this issue.


     


                


    krishna


    • Editado G Krishna domingo, 25 de marzo de 2012 17:33
    domingo, 25 de marzo de 2012 17:31
  • The error message is very explicit. You need to stop using old way of join operations and use ANSI LEFT or RIGHT JOIN.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    domingo, 25 de marzo de 2012 19:07
  •            My full database is now in SQL SERVER 2008 R2 &  i have more than 200 stored procedure.

    For now you can run in compatibility mode 80 as suggested by the error message. But please be aware that means that you will not be able to use all features in SQL 2008.

    However, as the message says, you are strongly recommend to change these procedures. Do you have 200 procedures in total, or 200 procedures that uses the old-style outer-join operators?

    Whatever, you need to start working now, and get rid of these ugly guys. In the system I work with, we had some 5000 stored procedures at time SQL 2005 was released. Of these about 500 used the old operators. We took the bull by the horn and changed them all.

    Also, very important: SQL Server 2012 does not support compatibility level 80 at all, so you need to fix your procedure before you can upgrade.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    domingo, 25 de marzo de 2012 19:25