Suppose I have an SQL Server 2008 which is installed on windows 2008. There is a db MyDb under the instance. MyDb is have full backup every night. Because of storage space on hard driver, only 7 days backup kept. the log on SQL Server is never truncated. Now the log file even is larger than 100GB. Then I am requested to restore the db to a day one month ago. how can I do that? Also can I shrink or truncate log file much smaller?

  • If you perform a full backup daily and you only have 7 days worth of backups, my math says 30 days > 7 days. Now, to mitigate against this, you could take a full backup weekly, daily differentials backups (as well as transaction log backups every 15 minutes), assuming you are in a recovery mode that supports this + this meets your RPO (recovery point objectives). This will help tame your transaction log size. Your differential backup will be much smaller and you might be able to retain backups longer. Best case, ship those backups to cheap disk – billinkc Nov 26 '13 at 1:04
  • For shrinking, see @mikeWalsh's excellent Q&A – billinkc Nov 26 '13 at 1:05
  • 2
    DBA Reactions – Kin Shah Nov 26 '13 at 1:17
  • I don't suppose you have filesystem level backups from a month ago with those backup files. You probably wouldn't be posting if you did. – Katherine Villyard Nov 26 '13 at 3:34
  • 4
    This question appears to be off-topic because it is about time travel (and doesn't demonstrate an understanding of basic backup mechanics). – Mark Storey-Smith Dec 1 '13 at 1:18

>I am requested to restore the db to a day one month ago. how can I do that?

you can't as you only have 7 days of backups due to disk space shortage. If you are/were moving the backups away and storing them for X amount of days then you would be able to go back that X amount of days to recover your data.

also, your database recovery model plays an important role in whether you can do a point-in-time restore or not - provided you are taking transaction log backups in full or bulk-logged recovery mode.

>Also can I shrink or truncate log file much smaller?

yes you can do it, but with some caution.

shrinking database is not a good idea unless you are sure that it won't grow to that point again.

refer to I Need to Shrink My Database - I just freed a lot of space for more details as it is covered is a nice and precise way.

| improve this answer | |
  • Thanks. Although there is no backup for the day one month ago, but all log is there in log file(which is more than 100 GB). Is it possible to use this log file and current backup to roll back the db to that day? – KentZhou Nov 26 '13 at 1:27
  • 2
    If you have not taken any log backups then your database is in pseudo-simple recovery as no log backup chain is established. – Kin Shah Nov 26 '13 at 3:20
  • No, a full backup establishes the log chain (a differential backup can also re-establish the log chain), not a log backup. It doesn't change the outcome, though, as there needs to be a full from before the desired point-in-time. – Jon Seigel Nov 26 '13 at 5:21

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