Attach files from SQL Server 7.0 to SQL 2000

  • I am trying to attach the database files of SQL 7.0 to SQL 2000. Its gives me an error

    Device activation error : the physical file name 'E:\mssql7\orderprocessing_log.ldf' may be incorrect.

    Device activation error : the physical file name 'E:\mssql7\data\orderprocessing_log1.ldf' may be incorrect.

    I also tried with single file attach but it still gives same problem.

    Please let me know how to reslove this issue, Client is waiting for me.

    Thanks

    Vduggaraju

    venkatesh


    venkatesh

  • It should work with no problem, it will auto upgrade the db to SQL2K compatible at the time its attached. I'd suggest first checking to make sure the db doesn't already exist on the server. Then, how many files total comprise the db? One mdb and two log files? All in the same folder? The attach dialog is actually pretty good, should tell if something is wrong.

    Andy

  • Hi Andy, Thanks for replaying to my mail. I tested in my test box it works fine with different database. But this database files are sent by client, one mdf file and one log file. When i try to attach is gives me an errors. Let me know it will store any data in the log file after detach the database from server. Please any one replay .

    Thanks

    Vduggaraju

    venkatesh


    venkatesh

  • Just disregard the log file - move it to another folder. Then try again. All transactions are completed before the db can be attached, so the log is not important.

    Andy

  • Hi Andy,

    I tried the same thing, like rename the log file and attach the single datafile with sp_attach_single_file_db, This gives the same error.

    Please Advise.

    Venkatesh

    venkatesh


    venkatesh

  • Can you run the sp_attach_db and post the exact code run and the exact error message from Query analyzer?

    Steve Jones

    steve@dkranch.net

  • Hi Steve, Here is the code ran and error messsage.

    EXEC sp_attach_db @dbname = N'PMOPS',

    @filename1 = N'E:\Program Files\Microsoft SQL Server\MSSQL\Data\OrderProcessing_data.mdf',

    @filename2 = N'E:\Program Files\Microsoft SQL Server\MSSQL\Data\OrderProcessing_log.ldf'

    error messsage:

    Server: Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'PMOPS'. CREATE DATABASE is aborted.

    Device activation error. The physical file name 'E:\MSSql7\Data\OrderProcessing_Log1.LDF' may be incorrect.

    Thanks venkatesh

    venkatesh


    venkatesh

  • Looks like it is expecting 2 log files, you're only specifying one.

    Andy

  • Hi Andy,

    By any way we can by pass the log files and create the database with only MDF file. IF yes let me know.

    one more thing i also tried with SP_attach_single_file_db, but it still gives same errors, asking for two log files.

    Help me if you have any solutions.

    venkatesh


    venkatesh

  • Microsoft Technet states the following;

    "Use sp_attach_db rather than sp_attach_single_file_db to attach a database with

    multiple log files. You cannot attach a database that has been created with

    multiple log files without also attaching all the log files."

    Article is Q271223

    It took me a few minutes to find this but I knew I had seen it before. Hopefully this will help.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David, thanks for the link. I think this will be your problem. This is one of the drawbacks of multiple files in a database.

    Steve Jones

    steve@dkranch.net

  • Thanks david and steve. I will check with the client for the all the log files.

    venkatesh


    venkatesh

  • Please follow up and let us know what happens.

    Steve Jones

    steve@dkranch.net

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply