Read-only Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Read-only Data

14 posts, Page 1 of 2

1 2 »»

Read-only Data

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Thursday, January 05, 2012 11:49 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:49 PM
Points: 31,432, Visits: 13,742

Comments posted to this topic are about the item Read-only Data

Follow me on Twitter: @way0utwest

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

Post #1231267

Revenant

Posted Friday, January 06, 2012 1:55 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 3,459, Visits: 2,535

Daily, like all other databases.

With 4 TB 10,500 rpm drives going for about $220, I could back up all databases twice a day.

Post #1231290

rf44

Posted Friday, January 06, 2012 3:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 48, Visits: 788

In the company where I work, the ultimate archive stage consists in exporting the data in a flat model (csv files) that are stored on CDs or DVDs (always in pairs, each member of a pair being stored in a different location).

Post #1231321

S Hodkinson

Posted Friday, January 06, 2012 3:41 AM

Old Hand

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:01 AM
Points: 384, Visits: 1,003

I'm with Revenant. I backed up daily ('belt and braces'). You only have to rebuild a server once to know that you never want to do it ever again.

Madame Artois

Post #1231345

scunningham 38251

Posted Friday, January 06, 2012 6:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:50 AM
Points: 136, Visits: 526

Here's a third vote for 'daily'.

The archive system I came up with requires management approval to move old stuff off so it only actually changes every few months. So really, just backing up after every archive operation would be enough.

But our backups run overnight, there's no load or user interaction to speak of then, and so slowdowns and such don't matter. So, I do do everything all in one shot, every night.

If it ever gets to be a problem I'll reduce frequency but so far nothing bad has come of it.

Post #1231433

Mike Palecek

Posted Friday, January 06, 2012 6:57 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:26 AM
Points: 845, Visits: 688

Here is another vote for daily.

When we run out of backup space on our dailies I will consider alternatives, but for now the convienece of having a single backup with everything in it is great.

Post #1231436

Ira L Grollman

Posted Friday, January 06, 2012 8:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 08, 2012 5:07 PM
Points: 4, Visits: 25

I use a disaster recovery plan for each client based on their ability to "know" where the archives are and their permitted recovery time. The issue of knowing where a backup is and its validity is a business process issue. It is one of those holy grail issues that are covered by corporate compliance and verified by the audit team. I recommend an internal replicated SharePoint site for those memory joggers with hardcopy (yes, paper) of critical pointers stored off-site in the organization's bank safe deposit box.

Usually, the required "back on the air time" dictates how "near real time" the archive backups are. In general the archives are read verified, if stored on-site, at a low priority as part of other regulatory compliance processes (SOX, ISO 9000, etc.). If the archive backup is off-site, then a new archive backup is created to the same schedule as the organization's fiscal reporting, usually monthly, or at a minimum annually.

FWIW, the disaster recovery plan (or business continuity if you prefer) is usually required for the organization's Errors and Omission insurance and sometimes its liability insurance.

Regards, Ira

Post #1231503

milzs

Posted Friday, January 06, 2012 10:08 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:32 AM
Points: 3,274, Visits: 296

One of our databases is populated on a monthly basis when we get a DVD from an outside agency. This data is converted from the DVD format and stored in a SIMPLE recovery model database. Once it's written that one time, it's read-only thereafter. So after we import the data, we make a backup, and don't backup until we repeat the cycle the following month.

Post #1231589

george sibbald

Posted Friday, January 06, 2012 10:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 5,269, Visits: 11,207

just to be different, if the data is not changing, I would probably go for weekly. Why waste the resources (including tape) if the data is not changing?

---------------------------------------------------------------------

Post #1231603

TravisDBA

Posted Friday, January 06, 2012 12:15 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:23 AM
Points: 1,288, Visits: 2,996

I have worked with read only data, particularly read only databases all the time and my concerns are not so much from the backup side of things, but more from what I can't do once I make that data Read Only. So, you need to make you sure complete these things before you set the database to READ ONLY mode, because once you set it, then:

•Statistics will not be automatically updated (nor required) and you would not be able to update statistics of a READ ONLY database
•READ ONLY databases will not shrink automatically or manually
•You will not be able to create indexes
•You will not be able to defragment indexes of a READ ONLY database
•READ ONLY databases will not allow you to add any extended properties on any of its objects
•Permissions may not be edited and users may not be added or removed from a READ ONLY database.

Backing up read only data is only a small part of a much bigger picture. BigGrin

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ... BigGrin

Post #1231665

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions