Backup/Restore, Simple Recovery model and transaction logs

colemancoleman Posts: 23
edited April 7, 2011 9:23PM in SQL Backup Previous Versions
Hello!

Its time I tried to understand some stuff a bit better. I use SQL Backup 6 to backup a database that's simple recovery model.

When we do restores, we generally restore to a different server and database, because we have to retrieve data using the application that sits atop the database. This has been problematic sometimes because the database keeps growing, so I've had to grow the disk space for restores. I've realized though that part of the problem is that the transaction log is something like 70GB as I usually let the daily backup do the checkpoint. I understand I can roll back transactions with the simple recovery model so that's why I still have one, but I don't follow why I need it in a restore operation, since I can do a point in time recovery anyway.

So, the key questions for me are:
- is it possible for me to do the restore and have the database come up functional without restoring the log file
- if not, how do I not backup the giant log file? Should I just run a shrink operation prior to the database backup?

Thanks!

Comments

  • peteypetey Posts: 2,358 New member
    - is it possible for me to do the restore and have the database come up functional without restoring the log file
    No.
    if not, how do I not backup the giant log file? Should I just run a shrink operation prior to the database backup?
    Depends. Is 70 GB for your trx log 'normal'? How much of it is actually in use? If 70 GB is 'normal', then you should not shrink the trx log as it'll affect your production database, because it'll just need to grow the trx log again. If 70 GB was the result of a one-off import process or index rebuilding process, then yes, you can shrink the trx log to slightly more than it's 'normal' size, whatever that may be.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.