1

I have a SQL Server 2012 Std Edition installed, contain 1 database in full recovery mode, so the log grown up quickly, but I just need a PIT recovery in past 7 days.

Can I schedule a full database backup every day (rolling 7 days) and truncate the t-log right after the backup job ? or what is the best way to keep the file size as small as possible ?

  • If PIT is 7 days, do you need to be in full recovery mode? Is it easier to just switch to simple? – Greg Aug 25 '16 at 3:32
  • How can PIT be done with simple recovery ? Sorry for newbie question. – Rex Lam Aug 25 '16 at 3:49
  • I might have misunderstood. Do you need to be able to restore to any point in time in the last 7 days, or can you afford to lose 7 days worth of data? – Greg Aug 25 '16 at 4:06
  • Yes, I need to restore to any point in time in the last 7 days actually :D – Rex Lam Aug 25 '16 at 4:09
2

If you need to be able to restore to any point in time then you need to be in full recovery mode (which you are). When in full recovery mode, you need to do transaction log backups in addition to your full backups.

Doing a Full Database backup does not make any difference to your log file, only a transaction log backup will clear it out and allow you to reuse the space.

When you're in full recovery mode NEVER truncate your transaction log - this will break the backup chain and you won't be able to do a point in time restore.

Transaction Log backups can run as frequently as you want or as often as you need to keep the file from growing bigger than your disk space or are required to by the business. I've seen some companies run them daily and some run them every minute and a lot in between. The advantage of running them more frequently is that if your disks die then you only lose data since your last log backup (assuming they're on different drives/hardware). It also keeps your log backup files smaller and the backup operation is usually faster because it has less to backup.

You can schedule a job to clean up old log backup files that you no longer need (e.g. in your case more than 7 days old).

If you need to restore from backups then you need to do the following:

  1. Restore last Full Backup
  2. Restore last differential backup (if any)
  3. Restore all log backups up to the point you want to restore to

Can I strongly suggest using Ola Hallengren's scripts if you're not using a 3rd party tool to do backups (https://ola.hallengren.com/)

  • Can I assume that I don't need differential backup if I run the t-log backup very frequently ? (say every hour) or may I ask why need differential backup if I'll perform the t-log backups ? Is t-log backup will free up the log space automatically at the same time ? – Rex Lam Aug 25 '16 at 4:27
  • Off topic, any suggestion for SQL server visualised monitoring tools that I can see the read/write operations/throughput, db connections ? better if it is free, thanks Greg ! – Rex Lam Aug 25 '16 at 4:38
  • 1
    Differential backups mean that you can restore faster. If you need to restore then you need to restore all log files since the last full or differential backup. So if it fails right before you do your full backup then you need to restore all of the log files for the week. If you do a diff backup every day then you need to restore the full, then the latest diff then the log files since the diff – Greg Aug 25 '16 at 5:22
  • @Greg; I'll second that, plus add that disk space is cheap. You cannot do your job (keeping data safe) if you do not get sufficient disk space. – Henrik Staun Poulsen Aug 25 '16 at 12:21
1

It depends on your company RPO and RTO. If it is ok or acceptable to lose some data you can just switch your database recovery mode to simple. But you have to know that you will no longer be able to perform a point in time restore.

The second and safer option is to keep your database in a full recovery mode and take more transaction log backup

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.