1

BACK STORY:
I'm a developer with a local instance of SQL Server 2008 on my workstation. I just recently upgraded my motherboard and found out (much to my dismay) the old motherboard's RAID drivers apparently are not compatible with the new mother board so essentially, I'm unable to use the Windows instance I had with the old motherboard. Luckily I have a full backup of all it's contents.

My problem:
I want to install SQL Server 2008 on my new box but I want to load the old data files. I have quite a few databases I don't want to recreate. I have all the original MDF's and LDB's but no actual SQL Server "backups".

Is there a way I can do this?

Thanks for your help.

4
  • To my knowledge NO. But you create the DB with same name manually and then point it's mdf and ldf files to the existing files you have. may work
    – Rahul
    Apr 17 '14 at 14:31
  • This site is for programming questions. You should be asking on the DBA SE site. But generally, you can "attach" a .mdb file into MSSQL, and you'll suddenly have the old db available.
    – Marc B
    Apr 17 '14 at 14:31
  • I don't know if this will help : technet.microsoft.com/en-us/library/…
    – MatBailie
    Apr 17 '14 at 14:31
  • MDB is MS Access - not SQL Server. SQL Server data files are .mdf (and .ndf), transaction log files are .ldf
    – marc_s
    Apr 18 '14 at 10:51
6

You can actually attach a database file.

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\PathToDataFile\DataFileName.mdf'), 
    (FILENAME = 'C:\PathToDataFile\LogFileName.ldf')
    FOR ATTACH; 

Modify the paths with the paths to your files. The trick is the FOR ATTACH clause.

You can attach a database by right-clicking on the Databases node then selecting the Attach item.

References:

How to: Attach a Database File to SQL Server Express

Attach a Database

1
  • 1
    As you will be installing a new instance of SQL Server you may need to reconcile the DB users to the new instance's logins. There are a few posts on DBA.SE that talk about this. Oct 10 '14 at 10:32
2

You can attach the database through Management studio, there is a possibility it or the log file might be corrupted, but the general case is that it should be fine. Be sure to take a copy before you experiment, but this kind of thing is exactly the reason you go for a modern RDBMS like SQL :)

http://technet.microsoft.com/en-us/library/ms190209(v=sql.100).aspx

0

Using tsql:

CREATE DATABASE MyDB ON (FILENAME = 'D:\MySQLServer\MyDB.mdf'), (FILENAME = 'D:\MySQLServer\MyDB_Log.ldf') FOR ATTACH;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.