Restore in Full Recovery model with no transaction log backups

Question

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)?

Answer 1

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:

1. Turn on transaction log backups to better protect your data in the event of another failure in the future.
2. Switch to simple recovery mode. Normally (there are always exceptions), this will prevent your transaction log from growing as much as it has. Your ability to restore remains the same as in full recovery and not bothering to take log backups.

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.

Answer 2

If your database is in full recovery mode, it is actually in pseudo simple recovery mode until the log backup chain is established.

You can find the script to check here - written by Paul Randal.

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?

Yes but You will loose all the transactions that happened after the last good FULL backup since you dont have any transaction log backups to do a point-in-time restore.

Best references :

1. Understanding Logging and Recovery in SQL Server
2. Managing Transaction Logs
3. SQL Server Transaction Log Management - free book by Tony Davis and Gail Shaw