3,812
I see the standard recovery model for GP SQL database is "SIMPLE" recovery model. I like to know if it is good to consider other recovery models such as "FULL" or "BULK LOGGED" for GP. "FULL" model will allow point-in-time restore of database, but it seems if multiple users are using the system, point-in-time recovery may not be reliable in cases such as where user may be posting at that point-in-time. Any thoughts and experience on this?
*This post is locked for comments
MS SQL database corruption can cause a lot of trouble to you due to the criticality of data presented in the database. I suggest you use Cigati SQL Recovery Tool one of the best tool to recover data from corrupt MDF databases from MS SQL Server.
Do you have a suspect SQL database? Download SQL Database Recovery tool to repair SQL database and restore data from it. Fix SQL database with MDF Recovery tool. All the recovered data can be saved into an MS SQL database file or in the form of SQL Script. It allows the user to preview recovered data in enabled before saving them. Also, this SQL Recovery software can recover BLOB, image or large page data from corrupt SQL databases.
MaxB,
This is a little late to the conversation....
SQL Server handles transactions very well. It's up to GP to wrap the transaction in code to make sure everything is complete like this.
==========
Begin Transaction
Insert Header Row to Headers table
error then - Rollback Transaction - quit
Insert Detail Row(s) to Details table
error then - Rollback Transaction - quit
Commit Transaction
==========
If your server crashed during the above process and you only made it to the Insert Header row then the incomplete transaction would be rolled back like it never happened.
If your server crashed after the above process then the complete transaction would be pulled from the transaction log and applied to the database.
Hope this helps,
Matt
You purchase quality computer components and maintain it with quality people to reduce, but not totally eliminate this possibility. To have what you seek would require a massive rewrite of the underlying GP SQL code where a collection of transactions is treated as one so if one portion fails the entire collection fails.
Thank you for your replies. I assume the log file is recording all the SQL statements in the back-end. For most GP transactions, GP updates multiple tables when user saves or does posting. So if we pick a point in time for restore or backup, it seems there is some possibility of broken records. for example header table is updated but line item table may not be updated. Since the log file is being recorded while users are entering data in GP. How can we avoid such cases?
If a point in time restore is not needed SIMPLE recovery will work fine. FULL would allow you to do a point in time restore so your schedule might be you do a full backup at night and then a transaction log backup every hour between 8am and 5pm. If you need to restore to 1pm, you would restore the full backup and then each log backup from 8am till 1pm. It all depends on how much data you can afford to lose and how much that lost time is worth. It has nothing to do with the number of users.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (