[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

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!
QUESTION
Does this mean that approx 12GB of log data has not been updated into the database?
What shall I do?
0
waltforbes
Asked:
waltforbes
  • 2
  • 2
2 Solutions
 
DcpKingCommented:
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:

http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too-big/

hth

Mike
0
 
waltforbesAuthor 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
0
 
DcpKingCommented:
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:
RESTORE DATABASE database_name WITH RECOVERY
(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.

hth

Mike
0
 
Scott PletcherSenior 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.
0
 
waltforbesAuthor 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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now