Post reply

moving databases from SQL Server 2005 to SQL Server 2008

  • January 6, 2010 at 12:45 pm

    #215207

    Hello Guys.

    Happy New Year !

    I am planning to move my databases from SQL Server 2005 to a new seperate SQL Server 2008. I then would set the compatibility for these databases to 90 :SQL Server 2005. Please let me know if the steps mentioned in the following link is apt for my case. http://support.microsoft.com/kb/314546.

    Is there anything else apart from this that I need to consider before/after moving.

    Would I need to transfer the logins if I am moving from one server to another in the same domain ?

    Thanks

    Shruti

  • January 7, 2010 at 6:54 am

    #1100165

    1.transfer the Server logins

    2.backup the database and restore it

    3.check the DTS packages to migrate

    4.after restoration change com.level to 100.

    Regards,
    Shivrudra W

  • January 7, 2010 at 7:03 am

    #1100176

    Thanks for the info, Is there an easier way to transfer the Server logins ? Also, im not sure about the compatibility of my application databases with SQL 2008. So i'd like to retain the com level to 90. So basically, Im just movign the databases from one server to another. In that case, my database would be still as it was in SQL 2005? Am i right? Do i need to consider anything else?

    Thanks

  • January 7, 2010 at 7:28 am

    #1100197

    Hi,

    To transfer user use:- http://support.microsoft.com/kb/918992 script

    -- like to suggest that check your application with 100 com level for test purpose it best way to find out dependency.

    Regards,
    Shivrudra W

  • January 14, 2010 at 3:17 pm

    #1103991

    It never hurts to set up a test SQL Server 2008 to test things out and find issues ahead of time. After setting up a virtual server with a SQL 2008 test, these are part of my 23 page process.

    Here are some of the highlights found on MSDN and Technet.

    1. Consider using DBCC Shrinkfile before relocation.

    2. when making the final full backups to restore - put SQL 2005 in Single user mode. Specify With FullRecovery option on restore.

    3. For making the final backup to restore - log in with the sQL Server 2008 Management Studio \100 (not \90)

    4. After restore set SQL 2008 Database Compatibility level to (100)

    5. For SQL 2008, leave recovery model to full. For some of my 2005 it was not necessary to do this. But, there are reasons you must do this in 2008. (but some exceptions exist if you use outer joins from a SQL 2000 version with *= or the =* operators.)

    6. If running a DB from SQL 2000 vintage, run the DBCC Updateusage after your restore. Important: fix any problems before continuing!

    7. Run DBCC CheckDB with All_ERRORMSGS before continuing.

    8 Run Exec SP_Updatestats on each DB (but be darn sure that your DB is not set at a compatibility level below 90!!!!)

    8. Microsoft suggests using the CHECKSUM page verify option as it offers more protection.

    9. Use a script to write a script on the master db that will create all users, group, and passwords. use keyword search on sp_Help_revlogin

    10. Do you have Linked Servers?

    11. Document your existing Jobs (i.e. backups, indexing...) to re-create on your new server.

    For those of you using Access Linked tables, there are some additional changes for the client side. It starts with updating the ODBC driver to include SQL 2008.

    Hope that helps get you started.

    http://www.sql-server-performance.com/articles/dba/migrating_databases_checklist_part1_p1.aspx

    is also good to review - update all 90 to 100 for the compatibilities.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply