Howto replace the ldf / mdf files of ms sql 2005 express edition with a stack of files from a different installation

Posted on 2007-08-03
Last Modified: 2012-06-22

I could need some help with the MS SQL 2005 Express Edition. We want to reuse datafiles from a different installation.
Right now I've got just standard dataspaces (oracle speech ;-) and I want to use a different dataspace, with all needed tables filled with the right data.

Is there some nice way to import that data, or is it suffcient to copy & paste the datafiles into the right folder?

This is what I've got for replacement:

17.760.256 master.mdf
2.883.584 mastlog.ldf
786.432 model.mdf
524.288 modellog.ldf
3.145.728 msdbdata.mdf
786.432 msdblog.ldf
8.388.608 tempdb.mdf
524.288 templog.ldf

These datafiles require the data to resist in the folder C:\Program Files\Microsoft SQL Server\MSSQL$BSI\Data\

Any ideas?

Question by:Tolomir
    LVL 27

    Assisted Solution


    You probably do not need to move the tempdb files, they are used to create scratch tables during queries. The tempdb is recreated every time you stop and restart SQL Server.

    LVL 9

    Accepted Solution

    You want to replace the System Databases?

    Is there a particular reason for that?  Normally this is a bad idea. :)

    Also, the TEMP DB is going to re-create itself everytime you stop/start SQL.

    Here is some information on System DBs for ya.
     - Brugh

    LVL 27

    Author Comment

    OK, I've got an update on this.

    The application we want to use is based on Microsoft SQL Server 2000 Desktop Engine.

    The drawback is, as it seems, there are just 2-3 concurrent connections allowed.

    We thus like to migrate from the small database to ms sql 2005 express server.

    Is there a way to accomplish this, maybe some data export / import?

    LVL 9

    Expert Comment

    I would check with yoru Application Vendor first.

    You still wouldn;t need to migrate your System DBs, just the USER DB(Application DB) and it should work without error. However, it all depends on the application that needs to access the DB.

    Also, some infromation on SQl 2005 Express limitations:

     - Brugh
    LVL 27

    Assisted Solution


    You are talking about moving the system databases from 2000 to 2005 and that will corrupt the 2005 database. The system databases are: Master, MSDB, tempDB, and Model. The Master database has everything about your system and database. The MSDB has everything about SQL Server jobs. The Model is used to create new databases, and as I mentioned before, tempDB is used for scratch tables.

    The issue is that there are significant differences between 2000 and 2005. You cannot just plug in the Master db from a 2000 server into 2005.

    I agree with Brugh, you should talk to your vendor first.

    Does your vendor support SQL 2005? If not, then any issues that you have from this moment forward will always come back "We don't know. We don't support SQL 2005." and if you have paid money for support you have just thrown it away.

    LVL 27

    Author Comment

    1st at all this currently is a test. I haven't bought the application, I'm just the one who as to check if the migration is possible.

    For sure I would not mix system databases of different versions, but when I asked the question, all I had were the datafiles. Now I got at least the application (30 days trial) and I found:

    MS SQL 2005 Server Upgrade Technical Resource Guide


    MS SQL Update Assistent...

    keep you updated.
    LVL 27

    Author Comment

    Thank you for the help.

    since this leads to nowhere, I redefined the question (since now I got much more detailed information about the needed version...)

    If you are interested checkout:

    How can I migrate a MS SQL 2000 Desktop Edition to the full (120 days trial) sql 2000 server?

    Thank you,

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now