March 7, 2014 at 12:52 pm
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.
March 7, 2014 at 1:08 pm
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.
March 7, 2014 at 1:24 pm
BTW, here is the KB article on how you can do that...
March 10, 2014 at 7:48 am
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.
March 10, 2014 at 8:02 am
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/
March 10, 2014 at 8:35 am
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
March 10, 2014 at 9:31 am
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.
March 10, 2014 at 1:01 pm
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...
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.
March 10, 2014 at 1:12 pm
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.
March 10, 2014 at 1:29 pm
Rod at work (3/10/2014)
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...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?
Nope! Recovery model has nothing to do with backups to fail in your case. Use the below code to create your batch file and let me know how this goes. It will create output file called backupjob_log.txt for your review.change your paths as needed. I tested this successfully just now on one of my Instances, ran just fine.
sqlcmd -S .\YourServer -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\', @backupType='F'" -o "C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\Backupjob_Log.txt"
March 11, 2014 at 7:48 am
Keith, in this situation I am OK with it. You see our application is written so that it saves the data in two places, at the same time. It saves it to the SQL Express database and then saves it to a backend SQL Server database. The backend database gets backed up much more frequently, so restoring from backup is no more than an hour off.
BTW, as an aside, we designed it to work like this because of how the previous version of this application use to be used. It had to be able to run on a laptop, saving the data all on the laptop. It might save several days worth of data to the laptop before it was brought back in house to have the data uploaded to the backend database. At this point the new application isn't going to be used that way. The infrastructure is in place to do it that way, but not fully implemented. We won't fully implement it, unless out users go back into the field to do work with the app.
Kindest Regards, Rod Connect with me on LinkedIn.
March 11, 2014 at 7:56 am
sreekanth bandarla (3/10/2014)
Rod at work (3/10/2014)
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...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?
Nope! Recovery model has nothing to do with backups to fail in your case. Use the below code to create your batch file and let me know how this goes. It will create output file called backupjob_log.txt for your review.change your paths as needed. I tested this successfully just now on one of my Instances, ran just fine.
sqlcmd -S .\YourServer -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\', @backupType='F'" -o "C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\Backupjob_Log.txt"
Here's what I've got in my batch file:
sqlcmd -U Superacct -P xxxx -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'"
In comparing your code and mine, the thing that looks different is you've specified "master..sp_BackupDatabases" whereas I've specified just "sp_BackupDatabases". Should I qualify that it start in the master DB?
Kindest Regards, Rod Connect with me on LinkedIn.
March 11, 2014 at 8:09 am
Rod at work (3/11/2014)
sreekanth bandarla (3/10/2014)
Rod at work (3/10/2014)
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...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?
Nope! Recovery model has nothing to do with backups to fail in your case. Use the below code to create your batch file and let me know how this goes. It will create output file called backupjob_log.txt for your review.change your paths as needed. I tested this successfully just now on one of my Instances, ran just fine.
sqlcmd -S .\YourServer -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\', @backupType='F'" -o "C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\Backupjob_Log.txt"
Here's what I've got in my batch file:
sqlcmd -U Superacct -P xxxx -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'"
In comparing your code and mine, the thing that looks different is you've specified "master..sp_BackupDatabases" whereas I've specified just "sp_BackupDatabases". Should I qualify that it start in the master DB?
Since I've deployed in master database on my test instance, I am using "master..proc". That's just my practice. Yes, You can give it a try. BTW, did you copied and pasted the sqlcmd command from the KB article or did you typed the sqlcmd command manually in a new file? The reason why am asking this is, There is a hidden char which is being copied over if we simply copy and paste the code from article!!! (Error Message: Sqlcmd: 'ûE': Unexpected argument). It's not visible in Notepad(but any hexeditor should expose it). So.....make sure you type this manually in a new text file and change the extension to cmd and give it a shot.
March 11, 2014 at 8:12 am
Also, you don't need to specify -E if you are using -U and -p switches and they are case sensitive.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply