Backup/Restore, Simple Recovery model and transaction logs
coleman
Posts: 23
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!
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8