Mount a SQL 2000 data and log file (MDF/LDF) directly to SQL 2008

My SQL 2000 server on a cluster crashed.  I don't want to rebuild it.  I plan to move all the MDF and LDF files to a SQL 2008 and mount them up.

  1. Copy all the data files (*.MDF) to new server (e.g.: D:\DATA).
  2. Copy all the log files (*.LDF) to new server (e.g.: D:\LOG).
  3. At the new server SQL Query Analyser, run:
    USE master;
    CREATE DATABASE mydb ON PRIMARY 
       (FILENAME = 
          'D:\data\mydb_data.mdf')
       LOG ON (FILENAME = 
          'D:\log\mydb_log.ldf')
       FOR ATTACH;
    
    
  4. (Optional)  If you have multiple files, use the below syntax:
    USE master;
    CREATE DATABASE mydb ON PRIMARY 
       (FILENAME = 
          'D:\data\mydb_data.mdf'),
       (FILENAME = 
          'D:\data\mydb_data1.mdf') 
       LOG ON (FILENAME = 
          'D:\log\mydb_log.ldf')
       FOR ATTACH;
    
  5. In order to access to the newly created databsae, you need to link database user name with the database login name:
    ALTER USER [foo] WITH LOGIN=[foo]

Comments

Popular Posts