Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004

    Unanswered: Recreating DB log files

    I would like to be able to regenerate the log files for our SQL databases without impacting the data files. I tried:
    - detaching the database,
    - saving the datafiles to another drive,
    - dropping and recreating the database with the same structure as before,
    - detaching the database again,
    - overlaying the data files with the ones I saved previously,
    - then reattaching the database.

    The reattach fails saying that the files can't be used for different databases.
    If I try to bring in log files that are the same size as the database log files I'm trying to recreate, I get the same error only the log files are the ones in error.
    I've tried recovering/recreating the database with the "for attach" parameter, but I get the same error.

    More information... The problem I'm having that requires the log files to be regenerated is realted to an upgrade to Win 2003 EE. We upgraded the server last week and since then, the backups have been failing. I've tried reloading a backup of the database but the only backups we have are prior to the upgrade and I think, because the database logs are operating system files, the logs don't get loaded correctly and I still have the same backup problem. I logged a case with Microsoft, but they haven't been able to help.

  2. #2
    Join Date
    Apr 2004
    Kansas City, MO
    They are probably like me and have no clue what you are talking about. Try to use:

    EXEC sp_attach_single_file_db @dbname = 'pubs',
    @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

    Forget the whole nonsense from point 3-5. Just use this command and attach as a database. If that doesn't work, you might be in for some fun.
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    So what is your actual problem? MS usually is very deep on issues related to upgrading to 2003, so I doubt they understood what you're trying to accomplish, thus - HO HELP. Can you step back and explain what went wrong?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2004
    Long Island
    Backup the database, recreate it with new data and log. Load the backup file to the new db. I do it when migration to new servers, works like a charm.


    FROM DISK = N'M:\Program Files\Microsoft SQL Server\MSSQL\DBBackup\backupfile.dmp'
    WITH MOVE 'datafile_dat' to 'I:\Program Files\Microsoft SQL Server\MSSQL\Data\datafile.mdf',
    MOVE 'logfile_log' to 'K:\Program Files\Microsoft SQL Server\MSSQL\DBLogs\logfile.ldf',

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts