SQL backup with Block-Level technology

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » SQL backup with Block-Level technology

SQL backup with Block-Level technology

Rate Topic

Display Mode

Topic Options

Author

Message

jwallace-613855

Posted Monday, January 21, 2008 7:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 19, 2009 12:45 PM
Points: 3, Visits: 13

Hi,

I am running Block-Level Backup software on all my servers. The backup software provider recommends putting my SQL Server Databases in Simple recovery mode. I am not sure whether this is a good idea. I can not find any documentation regarding block-level backup technology and SQL Server recovery models. The backup software does not provide an agent to truncate the logs so I would need to run a log truncate script. I tried the truncate only command but I receive an know error since I am in Full Recovery mode. Can anyone give me any direction on this.

Thanks,

Jeff

Post #445458

Joe Clifford

Posted Monday, January 21, 2008 11:01 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, January 07, 2013 11:28 PM
Points: 433, Visits: 618

Jeff -

By "block level backup" do you mean a specific backup package or are you utilizing the snapshot capabilities of a SAN/NAS?

There are SAN/NAS solutions that can provide point in time recovery for a SQL Server database (up to the most recent snapshot) and depending on the vendor you may be in good shape with some caveats:

(1) Make sure that the frequency and retention of the snapshots mirrors your recovery needs meaning that if you would normally take a tlog backup of your database every X minutes you need to be taking snapshots with the same frequency.

(2) Make sure that you continue to take & retain a snapshots at an interval that would allow you to recover from a database corruption in a worst case scenario (e.g. daily or weekly). Corruption, the creeping crud, whatever you want to call it happens... snapshots/block level backups will have the same corruption.

(3) Test, test, test. Have your SAN/NAS administrators restore a point in time snapshot to a new LUN/filearea and attempt to attach the database to a different machine... everything that's supposed to be there there?

(4) How is your data being sent off site? Replication to another site or tape? You'd be surprised how many times I've seen SAN/NAS storage not being backed up or sent off site ("but it's a SAN!, we don't need backup!"...) To point #3 above, test a recovery using just the offsite backup... that can get really interesting.

Joe

Post #445720

Minaz

Posted Monday, January 21, 2008 11:59 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 21, 2013 10:57 PM
Points: 535, Visits: 1,423

I should rather trust SQL Server backup/recovery then the tool you are using. If this is about the critical databases please have a good backup strategies.

"More Green More Oxygen !! Plant a tree today"

Post #445737

jwallace-613855

Posted Tuesday, January 22, 2008 5:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 19, 2009 12:45 PM
Points: 3, Visits: 13

Joe,

It is a software package called Filesx. My plan is to Backup every 15min to a disk and replicate the backup to a remote location and also backup the backup repository to tape. My problem is the truncations of the SQL logs. Should I put my database in Simple Recovery mode so I don't need to truncate the logs? I will be backing up my log files and my databases at the Block-Level every 15 minutes. I provides a full volume image and it has forever incremental capabilities. I have never put a critical database in simple recovery mode so it make me nervous because I don't know what to expect. Will it have an affect a performance or even it recovery capabilities?

Thanks,

Jeff

Post #445839

Steve Jones - SSC Editor

Posted Tuesday, January 22, 2008 9:29 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:54 PM
Points: 31,410, Visits: 13,726

I don't trust block level, and here is why.

Suppose I have a large transaction and it updates blocks 1-100. In the middle, I have a failure and blocks 1-99 were transferred, but 100 wasn't. How does the recover solution know this? The transaction would possibly be inprocess still and cause issues when you restarted SQL Server. It might have issues with rollback/rollforward.

Blocks work great for smaller files but because SQL depends on a consistent unit of work, which may span lots of blocks, this makes me extremely nervous.

I wouldn't run production databases in simple mode if I needed to recover to a point in time. If you can lose 15 minutes of data, then run in simple, but I'd err on the side of caution and leave them in full mode, running T-log backups with my fulls.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #445945

Donn Polix

Posted Tuesday, January 22, 2008 11:42 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:31 PM
Points: 89, Visits: 951

If you're recoverability requirement is point in time recovery, you'll need it in full mode. Keep your tlog backups as often as possible. You don't need to truncate the tlogs.

_____________
Donn Policarpio

Post #446032

jwallace-613855

Posted Tuesday, January 22, 2008 11:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 19, 2009 12:45 PM
Points: 3, Visits: 13

Hi,

It it ok to have a fixed size transation log, otherwise my transactions logs will grow about 2gb a day?

Thanks,

Jeff

Post #446035

Donn Polix

Posted Tuesday, January 22, 2008 12:18 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:31 PM
Points: 89, Visits: 951

It is a good practice to pre-allocate your tlog size, but you may want to leave it to auto-grow (unless you do not have plenty of disk space), otherwise you might find your db non-operational because it can't accept further transactions because your tlog is full. If you have a usual 2Gb trans/day, and you have a regular tlog backup, every hour, or every 30 or 15 mins, then you don't need to worry about your tlog growing. Of course, except when you run heavy trans that gets your tlog bloated quickly over your preallocated size, then it will auto-grow beyond it.

_____________
Donn Policarpio

Post #446060

Banks1850

Posted Thursday, February 21, 2013 7:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:04 PM
Points: 4, Visits: 86

We used doubletake forever at my old job, they use checksums to confirm a good write commit. And you can set it up to email or otherwise alert when the block level replication goes out of sync. It's actually a very solid way to do DR but it's resource expensive, that's the real drawback of block level replication, not reliability.

Post #1422846

GilaMonster

Posted Friday, February 22, 2013 3:25 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 37,678, Visits: 29,933

Please note: 5 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1422955

« Prev Topic | Next Topic »

Permissions