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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, bluefeet♦, Paul White♦ Dec 1 '13 at 23:23
>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.