Browse DevX
Sign up for e-mail newsletters from DevX


Creating a "Complete" Backup Solution-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Ensure a Reliable Network
If you've taken the time, effort, and money to create a hot backup solution, you might consider resting on your laurels. However, there is yet another hole to plug: the connection between your production and backup servers.

At one client of mine, a full backup of a SQL Server database took up over 20GB. Although the backup was successful, the restore process to the backup system would invariably fail before it completed. The two machines would lose network connectivity during the two hours it took before the restore could complete.

In another instance, even small transaction log backups couldn't be restored successfully. Because of a bad network, very subtle corruption took place during the copy. Of course, when this happened, the backup database was left in a suspect state and needed a full backup and restore to start over. (Yes, this was the same client I previously mentioned so you can imagine the dilemma I was in.) At the time, Microsoft recommended zipping the file and unzipping it on the backup machine before restoring it. If the file was corrupt, the checksum process in the Zip program would catch the corruption.

One technique I've seen used to good effect involves building a separate network segment (with additional network cards in each machine) just for the connections between the production machines. Because I'm not an expert on networks, one of the first things I do when setting up backups is to find someone who is and who can ensure that I won't have network-related problems.

For yet another network-related issue using SQL Server, see the following article from the MSDN Online Knowledge Base "Q137983: How to Troubleshoot Orphaned Connections in SQL Server."

Consider a Warm (Not Hot) Backup
After you install hot backups and build a solid network, can you relax? Sort of, except there's one thing you still need to worry about.

Hot backups are great because they are extremely current. The minute data is changed on the primary server, you propagate that change to your backup server.

But that very same close binding also comes with its own dangers. Consider what happens if someone accidentally deletes the wrong data. For example, say an administrator accidentally drops the accounts table in the database. (I'm not making this up; this actually happened at a client of mine.) The inadvertent deletion will immediately get propagated to the backup, too.

You can retrieve that lost information by restoring your last full backup and the transaction logs to the point before the deletion was made, but all that takes time. SQL Server 7 and SQL Server 2000 both include the STOPAT clause in the restore log command. When this clause is used, only transaction log records written before the specified time will be restored.

If every second of downtime is expensive, you should consider creating a copy of the database that is kept "warm"—i.e., fairly recent but not as recent as your hot backup. For example, you might set the hot backup within a minute of the primary server, but deliberately keep your warm backup 30 minutes behind your primary. That way, you only have to restore the last several transaction logs rather than start from scratch.

Constantly Reassess the Situation
How you can ever be sure you've covered all the bases? My personal answer is that you can't. For that reason, I always periodically reassess my backup plans to see whether there is any reasonable way I can improve them.

It's the only thing that can put me to sleep when I lie in bed wondering, What will crash next?

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date