February 16, 2004 at 10:31 am
I am pretty sure that a "Backup Log MyDB With NO_LOG" will cause problems when restoring transaction logs but just wanted someone to verify it for me.
If I have nightly full backups and hourly transaction backups and someone runs that statement, will I be able to restore properly a few transaction backups later?
I have a user that is running this statement because they said the transaction log was filling up on them two fast because of the large amount of data modifications they were doing, so he is using this statement to empty it.
Now, if I am right, at every hourly transaction backup the log file will truncate, right?
Also, if you are running in Simple mode, would running a CHECKPOINT be much better than the Backup Log With NO_LOG?
February 17, 2004 at 8:09 am
Running the NO_LOG statement eliminates your possibility for recovery to a point in time. Whenever your user runs this Backup with NO_LOG statement, you will only be able to recover to the last transaction log backup made before the statement was run. All of the transaction log backups made after that will be useless because part of the log is missing.
Example:
1am - Full DB backup
2am - Trans Log backup
...
8am - Trans Log backup
8:46am - Your user runs "Backup Log MyDB with NO_LOG" statement
9am - Trans Log backup
...
In this case you would only be able to recover to 8am. All of the Trans Log backups made after that are compromised until the next full DB backup.
Can you schedule the transaction log backups to run more often so that it doesn't fill up so fast and you don't lose your recoverability? If you change your recovery model to Simple you will only be able to restore from your last full DB backup.
Also, if you are running in Simple mode, would running a CHECKPOINT be much better than the Backup Log With NO_LOG?
From BOL:
When the database is using the simple recovery model, SQL server will automatically remove the inactive portion of the transaction log for you when the log is 70 percent full. You can't run a Backup Log statement if you are using simple recovery model.
I hope this helps.
Jarret
February 17, 2004 at 2:03 pm
Does the user run the script at the same time? If so, you could always schedule a FULL Backup to run after the script has finished. That way you will be able to restore up to the time s/he began the script (you'll have the full backup and all the transaction logs for the time between the script ending and it beginning again).
-SQLBill
February 17, 2004 at 2:17 pm
By the way....why does a USER have BACKUP permissions? I would NEVER give one of my users BACKUP privileges, I know how the database(s) need to be backed up to meet my managements requirements for recovery. I don't want a user messing that up - especially when I'M the one who will have to answer why I can't restore the database(s) properly.
-SQLBill
February 18, 2004 at 6:49 am
The user does not have backup permission on our production database. He just uses a development database to do his work and we are not concerned if he breaks anything there. I just wanted to verify what I had thought about the With NO_LOG option.
I also don't understand why the transaction log is filling up so fast. I post a new question for that though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply