Post reply

Migrating SQL 2000 Databases to SQL 2008

  • November 18, 2009 at 4:00 pm

    #214112

    We have about 75 databases. All 75 databases are almost structurally identical, only objects are tables and triggers. One Login object used for all databases, ID "MyLogin". Everything is nice and simple, been working great.

    Next week, we are building a new server: W2K8 Server Std 64-bit + SQL 2008 R2 Std 64 bit.

    I think the manual migration process for each of the 75 DBs is:

    a) Backup DBs - Example: DUMP DATABASE MyDB TO DISK='C:\MyDB.bak' WITH INIT

    b) Power down old SQL 2000 server.

    c) Create new Login object "MyLogin" on new SQL 2008

    d) Restore DBs - Example: RESTORE DATABASE MyDB FROM DISK = "C:\MyDB.bak"

    e) Change compatibility level to be native SQL 2008.

    Is it that simple, or am I missing some step(s)? Can I somehow batch the entire process via a sql script or powershell. Or maybe there's an Import across-the-wire function from SQL 2008 side?

    Any hints would be really appreciated, get me going in the right direction, and hopefully home for the holiday next weekend instead of DBA work at the office :).

    Thanks,

    John

  • November 18, 2009 at 9:15 pm

    #1081342

    Here use this:

    http://www.scalabilityexperts.com/default.asp?action=article&ID=90

    Alex S

  • November 18, 2009 at 11:26 pm

    #1081368

    I didn't see anything about the pre-work needed..

    Have you run the SQL Server Upgrade Assistant against the server to see what it points out?

    Are you aware of any code that might have compatibility issues in SQL 2008.

    Are you using xp_sendmail anywhere?

    Do you use DTS at all?

    Lots of things like that need to be considered first..

    CEWII

  • November 19, 2009 at 4:31 pm

    #1081896

    Elliott W (11/18/2009)

    I didn't see anything about the pre-work needed..

    Have you run the SQL Server Upgrade Assistant against the server to see what it points out?

    Are you aware of any code that might have compatibility issues in SQL 2008.

    Are you using xp_sendmail anywhere?

    Do you use DTS at all?

    Lots of things like that need to be considered first..

    CEWII

    Thanks for the reply. We're not using DTS, or sp_sendmail, or any other services that I'm aware of. Just the 75 databases, all accessed from client PCs via ODBC via VB front ends.

  • November 20, 2009 at 7:42 am

    #1082232

    AlexSQLForums (11/18/2009)

    Here use this:

    http://www.scalabilityexperts.com/default.asp?action=article&ID=90

    Thanks Alex, we already 99.99% sure the database(s) work fine under SQL 2008 since we've been testing a copy of SQL 2008 Express for awhile for basic single database compatibility, which is 100% fine. Our 75 databases are simply tables and rows being accessed by VB clients. Like MS Access, nothing fancy.

    I'm still trying to figure out best method to Migrate 75 databases from old 2000 box to new 2008 box.

  • November 20, 2009 at 7:51 am

    #1082240

    Just a couple of thoughts;

    - script out all logins; Have these in place prior to moving databases

    - all jobs from msdb

    - any user objects (tables, procedures) in system databases; check you might be surprised

    The backup / restore method is certainly a good way to do things. If the databases are on a SAN you could actually move the storage to the new server and then attach the databases. If you are doing that let me know as I added a script on this site for scripting out the "CREATE DATABASE FOR ATTACH" which will work nicely on the new 2008 instance.

    I'm assuming too that you have no replication based on your original post but if that is not the case then there are other caveats to this.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • November 20, 2009 at 4:23 pm

    #1082551

    Once you restore a database on 2005 you should do these tasks

    change compatiblilty level to 90 (make sure you've tested your application with this comp.level)

    execute a dbcc checkdb with data_purity

    execute a DBCC updateusage('db_name')

    execute a sp_updatestats

    Alex S

  • November 23, 2009 at 12:11 pm

    #1083102

    Thanks everyone.

    In addition to your great comments and hints, I found a very helpful article in one of the recent SqlServerCentral.com newsletters: http://www.sql-server-performance.com/articles/dba/migrating_databases_checklist_part1_p1.aspx and http://www.sql-server-performance.com/authors/shussain.aspx

    John

Viewing 8 posts - 1 through 7 (of 7 total)

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