Need to attach master database from another computer using MSDE

Posted on 2005-04-29
Last Modified: 2007-12-19
We are migrating a MSDE based application from one machine to another. But we are having problems trying to open the database in the new machine.

1. First we did a cold backup from the old machine. The application is mounted on a fork lift, so access to the machine is complicated. But we manage to gather all the mdf and ldf files. We didn't sp_dettach though.

2. We found out that the application data are located in the original master database.

3. We installed the same MSDE version (or at less, pretty sure it's the same) on the target machine. The new installed MSDE system has its own master, model, etc.

4. We tried to start the new MSDE installation from the acquired master.mdf and ldf files, but it won't. MSDE complains about an object ID 8762964 missing in the catalog, and should run DBCC checktable on the database.

5. We tried to do a sp_attach of the acquired database, but MSDE complains about a problem in the file header: "Msg 5172 16,15: mastlog.ldf is not a valid file header. PageAudit property incorrect".

6. We did succesfully sp_attach another database (not the master) from the same old machine.

7. The machines are quiet different. The old machine is a Windows98 tablet PC and the new one is a WindowsXP Tablet PC edition.

8. The old MSDE installation was pretty ill mantained. For instance, the ldf file is 1.2 Gb long for a 250Mb mdf file. In fact, the idea to migrate the application was to provide a more constant access to the application.

Please help, what should be our next move? I don't want to go to the "guessing" phase, and if we are going back to the warehouse, we should have a plan.
Question by:estrelow
    LVL 13

    Expert Comment

    You don't need to restore master database, just use EM DTS to transfer logins and EM Copy database wizard to transfer databases.
    LVL 13

    Accepted Solution

    EM is SQL Server Enterprise Manager
    DTS is Data Transformation Services
    Copy database wizard is available from mmc console menu Tools-Wizards-Management when you select anything under server node.
    LVL 32

    Expert Comment

    I will refer you to the following link:

    This is the most complete and correct listing of the steps needed to move a database from one server to another that I have seen.

    Note that your error about "mastlog.ldf is not a valid file header. PageAudit property incorrect" could indicate a version mismatch.  Look at the article referenced above to see your path forward.
    LVL 1

    Author Comment

    Ok, I'm taking ispaleny's advice.
    I just set up a Personal MSSQL 2000 to get access to EM (MSDE doesn't get it), working with EM makes a lot of sense.
    I successfully attached the old master database with a different name. I'm seeing the data now.
    I couldn't use database wizard though. I don't have network access to the running database, since it's on the fork lift.

    The production site should be MSDE though. Let's see if I find the way to go back to MSDE. Probably I'll end up buying a license.
    LVL 34

    Expert Comment

    Ym, you should never restore the master database from one machine to another--playing with fire.  There are just too many machine dependent settings to even attempt this type of restore.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    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

    20 Experts available now in Live!

    Get 1:1 Help Now