Read-only Data

Question

Post reply

Read-only Data

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
January 5, 2012 at 11:49 pm

#262569

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Revenant SSC-Forever Points: 42467 More actions · Answer 1

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.

rf44 SSCommitted Points: 1535 More actions · Answer 2

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).

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 3

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

scunningham 38251 Mr or Mrs. 500 Points: 587 More actions · Answer 4

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.

Mike Palecek SSCommitted Points: 1560 More actions · Answer 5

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.

Ira L Grollman SSC Journeyman Points: 78 More actions · Answer 6

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

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 7

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.

george sibbald SSC Guru Points: 104210 More actions · Answer 8

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?

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

TravisDBA SSCoach Points: 15780 More actions · Answer 9

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. 😀

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

george sibbald (1/6/2012)
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?

We had several read only databases. If the data is truly read only then I have no qualms with backing up that data less frequently (such as weekly or monthly). Regardless of the backup interval though, it is imperative that the restore of that database be tested.

edit: fixed the duh

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

george sibbald SSC Guru Points: 104210 More actions · Answer 11

If the data is truly read only then I have no qualms with not backing up that data less frequently.

sorry, what? 🙂

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

@george - fixed it

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

We keep 2 days worth of full backups along with Point-in-Time (PIT) logfiles for each full backup. Those, of course, rotate through every two days but not before the hit the tape backups. We even do that with the Development servers (although the PIT backups are less frequent)... it saves a lot of time when someone accidently blows up one of the tables or writes a really bad query that wipes out unintended data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)