Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Should I backup the transaction log? Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 12:52 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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,

Rod
Connect with me on LinkedIn.
Post #1548871
Posted Friday, March 7, 2014 1:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 292, Visits: 1,206
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.
Post #1548874
Posted Friday, March 7, 2014 1:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 292, Visits: 1,206
BTW, here is the KB article on how you can do that...
http://support.microsoft.com/kb/2019698
Post #1548880
Posted Monday, March 10, 2014 7:20 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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,

Rod
Connect with me on LinkedIn.
Post #1549215
Posted Monday, March 10, 2014 7:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 292, Visits: 1,206
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.
Post #1549230
Posted Monday, March 10, 2014 8:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:28 AM
Points: 2,114, Visits: 5,502
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/
Post #1549239
Posted Monday, March 10, 2014 8:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
This may answer some of your questions: http://www.sqlservercentral.com/articles/Administration/64582/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1549267
Posted Monday, March 10, 2014 9:31 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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,

Rod
Connect with me on LinkedIn.
Post #1549299
Posted Monday, March 10, 2014 1:01 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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,

Rod
Connect with me on LinkedIn.
Post #1549446
Posted Monday, March 10, 2014 1:12 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:21 AM
Points: 593, Visits: 929
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
Post #1549449
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse