Improve company productivity with a Business Account.Sign Up


Help with a SQL 2008 R2 Backup

Posted on 2010-08-18
Medium Priority
Last Modified: 2012-05-10
Hi Folks,

First let me say I know nothing about SQL backups, but I need to understand the logic behind what one of my database admin wants done.

 I have been asked to take over the backup of our SQL database.  The requirements are:

A rolling 7 day daily backup
A backup of the transaction log in such a way that the database can be restored to ANY point in time.  By that he means ANY time, a day ago, a week ago, a month ago, etc.
In addition to any SQL backup I do he wants a file level backup as well.

So here are my questions:

What is the best way to automatically backup a SQL database?
Currently the transaction log is 40 GB in size for a database that is only a few gig.  I have advised the DB admin that he could backup then shrink the transaction log to make it more manageable.  He says, no, he needs to keep all of the transactions in that log just in case he wants to roll the database back to ANY previous day.  Can anyone explain what you would want to do that?  Also, what is the best way to backup the transaction logs, shrink the log file and give the admin what he wants?
The admin also wants to have a file level backup done of the database.  If I am doing a SQL backup why do I need to do a file level backup as well?

Thanks in advance,

Question by:ehfrancisco
  • 2
LVL 14

Accepted Solution

mds-cos earned 2000 total points
ID: 33472092
There are a couple routes you can take, and I can make an argument for a couple being "best".  But I have landed on this route as my prefered approach.  Set up a maintenance plan on the database.  One of the maintenance items is backups, both for the database and for the transaction logs.  You can specify how many backup copies you want to keep.

Now, just so you can sound really smart if your DB does not like maint plans....setting up a backup device and scheduling backups to that device works A-OK too.  I usually opt for the maintenance plan route though because your database should have a maintenance plan regardless of how you choose to do backups!  So why not kill two birds with one stone?

As far as the requirement to be able to restore to ANY point in time...well that is generally just silly.  If you restore a database to a point in time 2 months ago I seriously doubt any of your users will be happy.  Frankly, on a busy database if you have to restore back to a week ago you just cost your company lots of money -- and made virtually every user hate you.  But I digress....

The "point in time" restore is accomplished via transaction log backups.  If you take a nightly backup of the database you get your "baseline" backup.  You can then take much smaller (and quicker) transaction log backups throughout the day.  If you need to restore, you restore the most recent database backup, then reply transaction log backups to get the database right to where you want it.  Consider carefully what interval you actually need for transaction logs.  If you work for a bank and this is a financial transactions database you may need to snap off a transaction log every 15 minutes.  If you work for a manufacturing company and this is the production line database, every couple hours may do.

I'm afraid at this point I have to say I think your DB admin is more of a programmer than a true DBA.  A transaction log should not EVER be that big on a small database!!!  If the transaction log gets big enough, the database will begin to exhibit significant performance issues.  Having the log file at 40Gb means that the transaction log is NOT GETTING BACKED UP (unless a lot of that size is free space, or this database is totally hammered all day long).  After the transaction log is successfully backed up, the log is flushed.  The way to restore to ANY point in time is by playing back the transaction logs from backup to whatever point in time you want to "roll back" to.

Ponder with me, then go educate your DB admin.  What happens if the database or server crashes?  Do you take the crashed transaction file and start playing it back?  No!  You start the restore process.  If you want to roll back your database, you do the same.  Restore to the latest baseline then play back transaction files from backups to the exact point of time you want to get to.

As to shrinking the log.  Well, the normal approach is just back up the transaction log then shrink the database.  But sometimes it simply won't shrink when a transaction log has been neglected for too long and grown out of control.  In this case, I find the simple way of shrinking it (don't tell your DB you are going to do this!) is to change the database type from full recovery to simple recovery.  This will clean up the transaction log.  Then set the database back to a full recovery model.  MAKE SURE YOUR BACKUP WORKED before you do this!  I have had to resort to this method a few times for customers, and have never had any issues.  But I would still never change database configs like this without having known good backups in hand.

File level backups?  OK,  I live in a paranoid world as well.  Given that some of the databases I manage are 24x7 I don't do file-level backups...but in the past I have (because I am a paranoid SOB).  The simple fact is you don't need to do a file level backup if your SQL backups are set up properly.  Even when I did do file backups, I never had to resort to using them.  Restore of the SQL backup has always worked for me.  But this does not mean there will never be a case when the SQL backup fails and the file backup works.  The big downside to taking a file backup is that you've got to take the database off-line during the backup window.  If you can do this then by all means, take both SQL and file backups ;-)

File-level backups can actually come in quite handy when making significant database changes.  Take the database offline, snap off a copy of the files, bring it back up and make the changes.  If something goes awry just revert back to the saved files instead of doing a SQL restore.

Hope this helps!


Author Comment

ID: 33476549

Thank you for such a quick and complete answer.  What you have outlined is pretty much what I have explained to my DBA.  However he has been very adamant about his requirements.

I have one follow up question regarding the file copy backup.  Using Volume Shadow Copy I am able to copy the files and folders in the data directory while the database is still online, with one exception. The transaction logs often (almost always) fail to copy, but that’s not my question, just a little history.  

My question is this, do I have to take the database offline while copying the files?  My logic says yes.  My reasoning is, this folder can grow to 85 GB and even though the utility I use compares the source and target folders and only copied files that have changed or been added, if I do not take the database offline some the files may become out of synch during the copy process because they are being updated as the copying takes place.  In other words, the copy will not be a static snapshot of the database instead it will be a mix of file changes made during the copy process.

Hopefully this all makes sense.

And thanks again for the great answer.

Author Closing Comment

ID: 33476577
This was a perfect answer.  Complete, concise and easy to understand.

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

579 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