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.
- Copy all the data files (*.MDF) to new server (e.g.: D:\DATA).
- Copy all the log files (*.LDF) to new server (e.g.: D:\LOG).
- 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;
- (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; - 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