SQL Server Backups

Points of My Scenario:
1. I am admin of a Windows 2008 R2 SQL server (domain member)
2. The OS of the SQL server is Windows Server 2008 R2, SP1
3. I successfully executed Full and Transaction log backups to separate files
4. The Full backup is 350MB, while the Transaction Log backup is a whopping 12GB!
Does this mean that approx 12GB of log data has not been updated into the database?
What shall I do?
waltforbesSenior IT SpecialistAsked:
Who is Participating?
DcpKingConnect With a Mentor Commented:
Pretty much, Walt.

1. Restore the database without recovering it (so it isn't ready to go yet ....)
2. In sequence, oldest-first, restore each transaction log backup SINCE the backup operation that created the database backup you just used. Again, these are restores WITH NORECOVERY.
3.  After you do the most recent transaction log, the database will be ready to go, so restore it completely:
(note that this restore command doesn't use a file name, because you've already got the database back from the file; all you're doing here is telling it that there's no more to do.

Refer here for more reading.


No - it should mean that you haven't backed up the log file in a long time!  (You're using Full recovery model, aren't you!)

So, now I've backed it up it'll shrink ?   ..... but (panic!!) it's still the same size!

Well, no, it won't shrink - it'll just be mostly empty!

The first thing you should do is establish a regular routine of data and transaction log backups I have no idea of your business requirements, so a full backup daily and a t-log one every 6 hours might be sufficient - or just one of each might... it depends on your business and the use of your db.

Anyhow, once you've established a regular routine you can think about reducing the size of the log. I'd recommend this blog entry as an excellent description of what you should do:



waltforbesSenior IT SpecialistAuthor Commented:
Hi Mike:
1. I do use Full Recovery model.
2. I backed up the T-log a second time: this second backup was ~610KB (what a reduction)!
TRUE/FALSE(?): If I wanted to do a restore to another SQL server, I must:
a. Execute a full-backup (on source SQL server)
b. Execute a t-log backup AFTER the full backup (on source SQL server)
c. Restore the full backup, follwed by the t-log backup to the destination SQL server
Scott PletcherConnect With a Mentor Senior DBACommented:
>> If I wanted to do a restore to another SQL server, I must ...<<

It depends.  If you want the restore to be to the last possible point-in-time in SQL, then yes.

If you just want a valid stand-alone copy of the db, you can just restore the full backup with recovery and go from there.

IOW, a restore does NOT require a trans log backup be restored for it to be valid/useable.  You only have to restore the log if you need the data brought forward past the original backup time.

>> Does this mean that approx 12GB of log data has not been updated into the database? <<

Not at all.  FULL recovery model "tells" SQL that you want it to keep everything on the log until you've backed up the log (no matter how big that means the log has to get).  SQL was really just doing what you told it you wanted to do.

You need to set the appropriate logging for your particular database (full or bulk-logged ("quasi-full") or simple).

Books Online has an excellent introductory primer on the differences among those different types.

You definitely need to shrink the log, a maximum shrink, to get rid of the excess VLFs.  Then *immediately* reallocate the log to the total size you will need.

Log files must always be pre-formatted by SQL, which takes a fair amount of time, so it's best to preallocate the space so that transaction time is not used on the fly to pre-format the log.
waltforbesSenior IT SpecialistAuthor Commented:
Many thanks to both of you; the increased understanding has empowered me to recommend [and implement] a backup strategy for the SQL server databases! Essentially, I will retain Full Recovery model, with regular scheduled transaction log backups - subsequent to database backups.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.