Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Should I backup the transaction log?


Should I backup the transaction log?

Author
Message
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1935
We're recently deployed a new application which uses SQL Server 2008 R2 Express for local storage. (Yes, I know that this forum is only for SQL Server 2005 Express, but I couldn't find an appropriate forum for SQL 2008 R2 Express.) Now, the express versions of SQL Server I know are not full fledged versions of SQL Server. In particular, there's no SQL Job Agent for SQL Express. I bring this up because normally I put the backup of databases and what's relevant to my question, the backup the transaction logs, into a SQL Job I schedule in SQL Job Agent in regular SQL Server.

So my question is, should we be backing up our SQL Server 2008 R2 Express databases and transaction logs? Especially to keep the transaction logs from growing too large on the users' machines? If so, how is that done?

Kindest Regards,RodConnect with me on LinkedIn.
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 1952
Hi Rod,
T-Log logging works the same way in both Enterprise Edition(Most expensive) and Express edition(Free of cost). All the same rules apply to T-Log management irrespective of what edition you are running on.
So, the answer is "YES", if your databases are not in "SIMPLE" recovery model.
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 1952
BTW, here is the KB article on how you can do that...
http://support.microsoft.com/kb/2019698
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1935
I just took a look at the recovery model used for my instance of the SQL Express database, and I see it is set to Simple. Under those circumstances, what should I do?

Kindest Regards,RodConnect with me on LinkedIn.
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 1952
You can't take a T-Log backup if your database is in Simple recovery mode. So, basically you can't do point in time restores. In other words, The earliest point you can go back(restore) is to the time you've your latest Full backup.
However, You could consider differential backups to minimize the data loss under SIMPLE recovery mode. If you want point in time recovery, you should be using FULL Recovery model and take T-Log backups frequently(with no log backups under FULL Recovery model, your T-Log will simple grow and grow......)

See this: http://technet.microsoft.com/en-us/library/ms191164(v=SQL.105).aspx
Hope this helps.
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
I look at it from a different angle. If you need the ability to recover from a catastrophic error by recovering a database to a specific point of time, then you have to do log backups. If you can't to lose more then just few minutes of data and doing full/differential backup might take to long, then you have to do a log backup. In that case you should modify the database's recovery model to full or bulk-logged (again it all depends on your business requirements) and start working with log backups. On the other hand if you don't need the ability of recovering a database to a specific point of time, and you can afford to lose data since your last full/differential backup, then you can set the database's recovery model to simple and forget about log backups. In any case I suggest that you'll read about recovery model and database backup and restore before you'll make up your mind.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
This may answer some of your questions: http://www.sqlservercentral.com/articles/Administration/64582/


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1935
You all are giving me great feedback here. I've got to consider what would be best for my situation. I'm sure that the user's SQL Express instances are all set at the simple recovery model; most likely that's the way it is "out of the box". The data is stored in the user's local SQL Express db and then moved to a backend SQL Server 2012 instance, where full recovery is the model and regular full backups with transaction log backups are done more frequently. I think that if a restore is done I could probably get it done through the backend server and then to the local SQL Express instances. Plus my users are already complaining that the app is slow; I can just imagine what would happen if I were to impose an hourly backup of the database on their systems. I might have a riot on my hands.

I'm leaning more towards keeping the recovery model as simple and doing a daily backup at a time when it's least likely to impact them.

Kindest Regards,RodConnect with me on LinkedIn.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1935
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...
http://support.microsoft.com/kb/2019698


sreekanth, I went to the link you provided. I've gotten the SQL script that's on the page, put it into its own .sql file and ran it in my SQL Express, putting the new SP into the master database. Then I created a .bat file, appropriate for my situation, and scheduled it into my Task Scheduler. I let it run at noon, my time. I then checked the status of the run; it ran fine. However, when I went to look to see if the .bak file was where I specified it should be, it wasn't. Is it because my recovery model is specified as Simple?

Kindest Regards,RodConnect with me on LinkedIn.
Keith Tate
Keith Tate
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 979
I'm leaning more towards keeping the recovery model as simple and doing a daily backup at a time when it's least likely to impact them.
If you are okay with the possibility of 24 hours of data loss and/or no ability to restore to a point in time then you should be okay. If either of those two things is an issue you may want to rethink your strategy.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search