I was given a MS-SQL server backup .bak file to restore on my machine which was about 25mb in size , but required 10Gb of free disk space because the log file was that size, which indicates that they log file is not getting backed out nor truncated. (Note that the 10Gb log file must be mostly empty otherwise the .bak file would be a lot bigger than 25mb)
This database is for a little used (and possibly not very important) database, and the same site has another much more important and much larger database, (size somewhere in the region of 5 Gb) which has a smaller log file, so I am assuming the log file for that database is getting trimmed.
I am told that the site uses Log shipping to backup their main database to a second server, but I'm not sure if the smaller database is also being backed up.
So, I am guessing that either they are only using log shipping for the main database or they are only backing up the main database.
Which raises a few questions
- Is there any sort of backup that a user/administrator on site can perform that interferes with or breaks log shipping?
- If you use log shipping, is that enough to ensure the logs don't continually grow, or do you still need to backup the logs (using for example a maintenance plan)?
- This one isn't too important, but if I ever get a backup that needs more space for a log file than I have available, is there any way of restoring it without the log file or without fixing the issue at source and getting a new backup.