Creativity in Database Backups

That’s right it’s time to go all Martha Stuart on this DR plan. And by that I do not mean cheat the system and call your co-workers worthless humans.

I am about to discuss Backup Stripping and is it best to send large backups across the network just to keep them off your local system.

“Marley was dead, to begin with. There is no doubt whatever about that.” Now I am not Marley but I am a SQL Lumberjack and I was faced with an interesting scenario that needed immediate attention.  I needed to create a DR plan and I needed to find a place to keep my backups. The full backup I was working with was about 60gig on average. I called up my NAS dudes and was told “we don’t have the space for a full backup that size”.  Ahh, but you do have some space. This is where your DBA creativity skills need to come into play.

Backup Stripping is when a backup is stored in equal chunks across a device but needs to stay on either a tape or disk. It’s my understand that it cannot be on both. It’s supported to single media type only. So we are taking our full backup and slicing it in equal parts. This will also increase your backup throughput. You as the DBA will not have control on how the data is divided SQL Server will attempt to keep the parts equal but if they are different do not be alarmed. This is determined by the speed of the disks. Slower disk may cause some bottlenecks and create unequal chucks of data. It is recommended you try to keep your chucks equal so try to keep your backups on disks that are similar in speed and performance. Additionally if you are going to be creative and are limited on space you can send your chucks of data to different servers.  Yes, this can make recovery more difficult but at least there will be a recovery model.

What is a SQL Blog without some scripting right?

backup database [sqllumberjack] to
disk= ‘d:\temp\sqllumberjack_1.bak’, –D: and E: backups are staying on the local system
disk= N’\\2ndServername\D$\sqllumberjack from dbhostserver\sqllumberjack_3.bak’, –These are being sent to another location
disk= N’\\2ndServername\D$\sqllumberjack from dbhostserver\sqllumberjack_4.bak’

Files sqllumberjack_1.bak, sqllumberjack_2.bak, will be similar in size, whereas files sqllumberjack_3.bak, sqllumberjack_4.bak are similar in size. However, files sqllumberjack_1.bak, sqllumberjack_2.bak, may be significantly larger than files sqllumberjack_3.bak, sqllumberjack_4.bak.

In both scenarios, drive D may run out of space, and you will get an error before the backup completes if drive D has limited space. If you must have files evenly distributed on both disks, you need to use disks that have comparable I/O throughputs.

The script above brings me into my next piece.

Is it best to send large backups across the network just to keep them off your local system? So what is a large back-up. That is for you, the DBA to decide. As a SQL Lumberjack you may have a slow network, with an old slower server, which is limited on space already. I would recommend keeping any backup larger than 2 gig on your local server IF and ONLY IF your server dudes are backing up the server nightly. This can reduce the I/O throughput across the network. We need to remember as SQL Lumberjacks we do not have another server readily available. There is no HA environment, we relay strictly on DR plans that do not have HA. Mirroring PLEASE! “Can I get a witneeess!” No, you cannot. We are straight up gangsters. These are for non-critical applications and non-critical databases after all. Any company will spend the money on HA if it’s a critical database. When a company bids out services, if they are bidding on a critical applications they will adjust to include HA, least they should.

No back-up server readily available. Worst case scenario the server catches on fire. Even if you send your backups across the network to another server you do not have a server to recover to. Your company has to buy a new one. What is the difference if your backups are local or in data center somewhere. Your server dudes have been backing up the server to tape. Those tapes will be used to recover the server. Even if just your database takes a dive, you can still relay on those tapes to recover.

I know I know, point in time recover. Your database has many transactions. You should send your t-logs across the network to your data center. T-logs are small they will not slow down the network. T-logs are your point in time recovery if you have to recover from the tape.

Chances are you will not even touch the full backups in the data center.


About Erik Disparti

I started my IT career in 1999 and in January 2011 I became a SQL DBA. So far I'm loving every minute of it and learning more and more everyday.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s