Post reply

RESTORE DATABASE from backup, but exclude LDF

  • February 17, 2006 at 4:09 am

    #111256

    I have a backup file which I need to restore to a MSSQL 2000 server. However, the LDF file has a declared size of over 5GB, and I do not have that much spare diskspace (please don't ask why). Is there a way to restore the MDF without the LDF? I have tried using:

    WITH Replace, NORECOVERY, MOVE

    but this fails due to insufficient disk space.

  • February 17, 2006 at 6:41 am

    #621627

    Hello Andy,

    If you have the complete backup file, then it would not be possible to restore only the .mdf file as it is a full backup consisting of both the files.

    Another way, you can do is to detach the database and copy only the .mdf file to the other server and re-attach the file (if you are sure that you don't need the transactions that have not been committed to the database which are in .ldf file). By default, SQL Server creates a new .ldf file for you.

    Thanks and have a nice day!!!


    Lucky

  • February 17, 2006 at 6:55 am

    #621637

    Unfortunately I only have the complete backup, not the individual MDF and LDF files. I know that with the MDF, I could use sp_attach_single_file_db

    to attach the database and a new (blank) LDF file would be created.

    But I don't have that luxury, nor do I have the diskspace to restore a 5GB LDF (which I know is not necessary). So that leaves me a bit stuck.

    Thanks for your post though.

  • February 17, 2006 at 7:05 am

    #621641

    Is it possible to attach a USB drive or some external drive to the server and then restore the database and put the LDF file on that drive.  You can then manipulate the database through detach-attach.

     

  • February 17, 2006 at 7:33 am

    #621658

    Yea, I guess I could do something along those lines (although I seem to remember having serious problems with MSSQL seeing virtual drives, so I'm not sure this would work).

    I have now circumvented the problem (a new backup file was supplied with a truncated LDF). But from a theoretical point of view, is it possible?

  • February 17, 2006 at 8:27 am

    #621687

    I've been able to put data files on external drives as long as windows see's the drive as a physical drive letter.

Viewing 6 posts - 1 through 5 (of 5 total)

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