SQL Server Backups

Posted on 2012-09-09
Last Modified: 2012-09-10
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?
Question by:waltforbes
    LVL 16

    Expert Comment

    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:



    Author Comment

    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
    LVL 16

    Accepted Solution

    Pretty much, Walt.

    1. Restore the database without recovering it (so it isn't ready to go yet ....)
    RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY
    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.


    LVL 68

    Assisted Solution

    >> 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.

    Author Closing Comment

    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.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
    Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
    This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
    This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now