Black hole in backup plan?

[Helmars]

Hello,

I noticed that sql server 2005 database backups always truncate transaction logs, but I don't see where they go. What happens to them? I can't find a way to restore a database to a point between end of database backup and previous log backup. Is such action possible?

Helm?rs

 

[mutley1]

Helmars,

Is your database if full or simple recovery mode? A tran log backup will remove the inactive portion of the log when it is backed up (i.e. it will back up what has been written to the database so that your log does not keep growing). However, if it is in simple mode, then it will truncate it on checkpoint. It depends on the set up you have for the particular database. Let us know for further explanation.

HTH,

M.

 

[Helmars]

Recovery model is "full". My question was about full database backups. It seems to me that transaction logs created between a log backup and start of full database backup are just deleted.
Consider this scenario:
1) full database backup;
...
2) log backup;
3) good transaction committed;
4) bad transaction committed;
5) full database backup.
How do I restore the database to time between points 3 and 4?

Helm?rs

 

[mutley1]

That is the correct SQL nature. Once you have a full backup, you no longer require the tran logs. I suggest you read up on the full recovery model.

1. Full Backup - OK, all up to speed.
2. Differential backup - any changes made since the last full backup.
3. Tran Log Backup - anything that has happened since the last full backup.

So if you want to restore to a point in time, you load the last full, and diffs inbetween and then any logs after.

If you do not use diffs then the last full and all logs since that backup.

If you say the tran logs are being deleted, then check your jobs as they might have a retention policy (i.e. keep for 1 day). If you are backng up weekly and only keping tran logs for 1 day then that is not going to let you restore to a point in time, unless it is within 24 hrs of of the last full backup.

Without knowing your full regime it is difficult to comment, unless you can explain what you mean by "where do they go"? they go to a .trn (by defaut) backup file.


Rgds,

M.

 

[Helmars]

This has nothing to do with retention time and I'm not speaking about any particular database, now I'm just playing with test database containing 1 table.
I can ask the question again:

1) full database backup;
...
2) log backup;
3) good transaction committed;
4) bad transaction committed;
5) full database backup;
6) other transaction;
7) log backup;
.....

Is it possible to restore the database to time between points 3 and 4?
Log backup 2 of course doesn't include transaction 3. Full backup 5 can't be used to restore database to earlier time. And it seems that log backup 7 contains only transactions since end of full backup 5. Is that correct?
Restore dialog of sql server management studio allows to choose backup 1 and log backups 2 and 7 to restore to point between 3 and 4, but this error is displayed:
"System.Data.SqlClient.SqlError: The tail of the log for the database "testdb_restored" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.Smo)

 

[mutley1]

allows to choose backup 1 and log backups 2 and 7".

You should not be going beyond step 4 (if not 3) in your restore given the above scenario. R

Restore step 1 with NORECOVERY, step 2 with NORECOVERY *UNTIL* the last log. If you know what time the problem occured then you can use the last log (with a dodgy transaction) but use point in time to stop to stop it before the dodgy transaction is loaded. If you are unsure, run the tran log restores and on the last known good one do a WITH RECOVERY.

I suspect the error you are receiving is because you are trying to load tran logs after the second full backup (step 5) but specifying a STOPAT prior to that full backup?

Unsure without seeing your full script.

HTH,

M.

 

[SQLBill]

Helmars,

It is a 'good practice' to do a transaction log backup just before a full backup just for the reason you state.

It is also good practice that if you find out an 'accident' happened, that you immediately do a transaction log backup.

Without a transaction log backup after the 'accident' happened, you can't restore to that point. Per your scenerio no transaction log has the moment before the bad transaction. Full Backups have to be restored in their entirety.

One 'painful' option, if you know what the bad transaction was, you can restore the earlier full backup, all transaction logs. Then restore the late full backup as another name. Write code or use a data compare program to compare the data in the two databases and make any changes to the original database based on the later fullbackup ignoring the bad transaction.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875