Just realized one of our databases has been in Full Recovery mode but we've not been making log backups. On a nightly basis, we've been running a job that runs this T-SQL command to do the database backup:
BACKUP DATABASE [foo] TO DISK = N'J:\NAV Backup\foo.bak' WITH NOFORMAT, INIT, NAME = N'Foo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
After searching the intertubes, I'm still uncertain about what would have happened if we would have had to restore with only just the .bak file (no transactional log file or backup of the transaction log). Would we have been able to get back to the point of the last nightly backup?
Assuming a restore would have worked, would the new transaction log been has big as the old (lost) one (~300GB in our case)?
Best Answer
Yes, you should be able to restore and you'd be at the point in time of the backup.
Restoring from a backup will literally create a physically identical structure as existed at the point of the backup - data and log files will be the same size, so yes, you will still have a 300 GB log file if that was its size at the time of the backup (even if it was only 1% full).
Now, you need to do one of two things once you're back up and running:
For a lot more information about what you should do next (including what to do about your 300 GB log file), please take the time to read this question and its answers - yes, it's a lot of information, but it's going to be invaluable, I promise:
Why Does the Transaction Log Keep Growing or Run Out of Space?
Your gut reaction may be to try to shrink the log file to as small as possible, but that's not necessarily a good idea.