Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2007
    Posts
    12

    Unanswered: Restore without restoring empty transaction log

    Here is the problem description:
    We have in production a database with huge log size (>20 Gb).
    I'd like to restore this database on another server BUT there is not enough disk space to restore the log.

    What we do now:
    We truncate the log before the backup.
    This isn't ideal since the transaction log has to grow again.

    Do you have any suggestion how we could work without truncating the log?

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    Thought about a different recovery model?

    Thought about your backup frequency? Which should make your log smaller if you increase your backup frequency.

  3. #3
    Join Date
    Jun 2007
    Posts
    12
    Thanks you for your answer.

    BUT

    A different recovery model is not possible: it is production.

    The database where I want to restore is another machine (not a disaster restore).

    Backup frequency is not an issue:the log file is big but the log content is small at that time. When I do a shink, the log file will shink form Gb to some Mb.

    I want to avoid having to shink the log file before backup and re-expand it after backup is done.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's not stated what your second installation is for, so I don't know if this is applicable, but have you considered replication?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2007
    Posts
    12
    The restored database will be used as a dev / test environment
    + again other will be used as pre-production

  6. #6
    Join Date
    Apr 2009
    Posts
    4
    Before taking the database backup change the recovery mode simple.
    after changing the recovery mode you can shrink your DB logs.. by changing the recovery mode to simple can truncate your whole current database logs.. Now you can generate the database backup and restore it on diff. server.. here now you can configure auto shrink so that you need not delete your database logs manually. and you can save you disk space...

    Cheers...
    Nitin

  7. #7
    Join Date
    Jun 2007
    Posts
    12
    Changing to simple mode is not an option: these databases are mirrored and it is production (transaction logs are needed).
    The shrink of the databases gives no problem. The truncate shrinks it to a reasonable size (so my problem isn't there).

    The problem I have is that, since I've done a shink, I need to regrow the log file after the backup has been taken.
    This is to prevent autogrowth during the day and it's easier to see the amount of space left on disk.

    So If I could avoid to shrink it, it would save time/ressource usage/reduce disk fragmentation/avoid autogrowth waits.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've very crudely tested applying snapshot replication, dropping the subscription and editing the database. This works.

    This would, however, probably have a greater resource implication than shrinking and growing the log.

    I assume this has already been considered and rejected, but have you got a slot for another disk? Apart from anything else, if my prod db has some large process that grows the log to 20GB then I would want my test environment to be able to handle that same process.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yeah - don't forget to set the recovery of the subscription db to bulk_logged.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2007
    Posts
    12

    Question No better work around

    Hi pootle flump,

    Thanks for your ideas.

    We did not even consider snapshot replication as it has a huge impact on the prod server as you stated.

    Another advantage of backup/restore is that it comes for free (no additional ressources needed) on the production server since you need a backup anyhow.

    I begin to think that there is no work around.
    It seems we will have to shink / grow log files.

    I'd like to thank everyone that came with suggestions.

Posting Permissions

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