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?
closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, Taryn♦, Paul White♦ Dec 1 '13 at 23:23
- This question does not appear to belong here. Either it's not database-related or it otherwise conflicts with the scope of our site. See What topics can I ask about here?, What types of questions should I avoid asking? or this blog post for more info.
>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.