We help IT Professionals succeed at work.

SQL database Backups

Robert3rd
Robert3rd asked
on
My company uses Microsoft SQL for our database.  We are a logistics company.  We do daily backups.  But we have gotten to a point that our backup from yesterday would be too old to restore incase a disaster strikes.  Transaction logs seems cumbersome to restore along with yesterdays backup.  So for example a credit card company's hard drive crashes.  so a purchase today will be lost if we were to restore from a yesterdays backup.  What would be the best backup/restore to such a up to the minute type of server?  
Comment
Watch Question

Commented:
Best scenario would be to set up SQL Server in a cluster configuration, that way if one server in the cluster fails, other servers in the cluster pick up the load with no data loss.  Couple that with off-site, real-time replication for a truly resilient configuration.  Gets expensive though.

For a simpler solution, you can set up a second SQL server, and enable real-time replication between the two.  Should the primary/production server go down, you'd have to make some changes on the back end to get your apps to point to the replica database, but you likely wouldn't lose any data.

Best management practices are to develop a robust system, make regular backups (disk to disk, disk to tape, replicate databases, etc.), and then routinely test those backups.
Distinguished Expert 2019
Commented:
You could use a backup product with continual data protection, Backup Exec CDP for example although you'll have to buy it again as soon as you change your SQL version to a later one unless you pay for software assurance.

Doubletake - http://www.visionsolutions.com/Products/DT-Avail.aspx You'd need 2 windows server licenses and 2 SQL licenses if you want live failover, although I think you cn get away with just 1 SQL license if you manually start up the SQL service on the DR box but at least it's Standard rather than Enterprise SQL and Windows licensing

Author

Commented:
We had a scenario that someone changed the quantity of a product to all our warehouse.  So our servers didnt crash but it displayed the wrong information but we continued to work on and it wasnt discovered till later.  so needless to say, if we restored from yesterdays backup, all the work after would have been gone.  so replicating will also replicate the problem to the backup or second server.  I guess i was wondering like how does credit card companies do this?  Probably hourly backups, but even with that you still have transaction logs to deal with to keep synced.  is there even a solution out there?      
Distinguished Expert 2019

Commented:
They [credit card companies] probably do a dual phase commit. The client software in the ATMs sends the transaction request to two or more databases and doesn't let you have the cash until it is registered by all of them.