Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009

    Unanswered: Restore Database Issue


    First time posting to the forum. Not an everyday SQL user. I checked the existing posts as well as read the FAQ and hope I have all the required information.

    Environment is:
    MS Windows 2K Advanced Server SP5 (Update Rollup 1 for SP4)
    MS SQL 2K Standard Edition

    I received an error when running a DBCC on a database on a test server. I decided to delete it, recreate it, and then restore from a backup.

    Due to available space issues on the server I had to place the backup db file on the same drive as the mdf and ldf files. Let's call it the D: drive. (I know having mdf and ldf files on the same drive is not recommended but that is not an issue right now. At least I think it isn't).

    A RESTORE FILELIST showed that the backup file is 12.5GB in size while the ldf file is set for 5.5GB. With the 10GB backup file on the same drive I had 19GB free to work with on the D: drive. The combo of the mdf and ldf file sizes leaves 1GB free to work with, which I am sure is not enough.

    So In order to make sure I had enough free space to work with I moved the backup file to an externally attached USB drive, call it Z:. This would give me enough free space on D: to restore the mdf file there. The ldf file would now be restored to another drive (M which has 4GB of free space to work with.

    However, after a good 15 minutes of running the following command:

    FROM DISK = 'Z:\live.bak'
    MOVE 'live_Data' TO 'D:\Data\test.mdf',
    MOVE 'live_Log' TO 'M:\logs\test.ldf'

    I received the following message:

    Microsoft SQL-DMO (ODBC SQLState: 42000)
    Could not adjust the space allocation for file 'live_Data'.
    RESTORE DATABASE is terminating abnormally.

    Any guidance is appreciated.

  2. #2
    Join Date
    Jun 2007
    Ohio, USA
    I don't believe you need to create the database first, in order to restore it. I would do this restore without creating the DB first.

    Also, is autogrowth enabled on the DB you created? Just guessing based on the error message.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  3. #3
    Join Date
    Jan 2009

    Thanks for the reply. Yes, auto growth is enabled.

    I will try restoring without first creating. I need to drop the database at this time as it is in the Suspect\Loading state. I'll post my findings soon.

  4. #4
    Join Date
    Jun 2004
    Long Island
    Sounds like you do not have enough space on target for .mdf file

  5. #5
    Join Date
    Jan 2009
    That was my line of thinking after seeing the error message. I am shrinking the database then backing it up and see what that gets me. The test server I am attempting to restore to is very limited on space as you read I am using an external drive in this effort. I'll post my efforts as soon as they are done. Thanks for your input.

Posting Permissions

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