Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

No! Don't do that if you want to restore at a point in time from that specific log backup. Please read How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008. I'd say you want to make a normal log backup and then switch to SIMPLE recovery model. And only then shrink the log file.
–
MarianJan 25 '13 at 19:36

Let's say if i don't need the transaction before Future full backup 1. But I want to be able to do a restore to point of time in the future.
–
gavinJan 25 '13 at 19:40

For a database set to full recovery mode to keep log transactions it needs an initial backup to set the starting lsn of the transaction log chain otherwise it's in pseudo-SIMPLE mode sqlskills.com/blogs/paul/…
–
MartinCJan 27 '13 at 1:24

3 Answers
3

will I be able to do a restore to a point in time between Future full backup 1 and 2?

Yes. What you're concerned about is the Log Chain. The log chain starts at a full backup. Therefore at future full backup 1 you will start a new log chain and with transaction log backups you will be able to restore to point-in-time.

As for your initial method of getting your log truncated, note the following from BOL on BACKUP:

The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model.

And I'm guessing this goes without saying, but I'll be explicit. In order to not run into this same problem you do need to be doing routine transaction log backups.

The main issue here is it looks like you're not doing any regular log backups. The first thing I would do is make sure you understand SQL Server's Recovery Models. Primarily, databases in FULL and BULKLOGGED need to have regular log backups taken.

I would recommend that to clean up your log file, first take a log backup:

BACKUP LOG foo TO DISK='<backup file location>'

Once this is complete, you can then safely execute your DBCC SHRINKFILE command to resize the log file appropriately.

I just went through figuring out SQL backups, and had a very similar question.

To restore to a point-in-time you need:

The most recent Full backup from before your point-in-time

(Optional) The most recent Differential backup after your Full backup and before your point-in-time

All the Transaction Log backups since the Full Backup or Differential Backup that you are restoring from

When you run

Backup log dbname with truncate_only

you are not backing up the log, you are erasing it. So the only restore you can do is the restore from your Full (or Differential) backup.

Mike Fal's answer shows how you can setup Transaction log backups, while Thomas' answer explains the log chain, but I felt the need to post exactly what you needed in order to restore to a point-in-time, since I had the same question last week and was very confused about what exactly I needed in order to do a point-in-time restore.