Alternatives to SQL Backups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Alternatives to SQL Backups

12 posts, Page 1 of 2

1 2 »»

Alternatives to SQL Backups

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Saturday, October 19, 2002 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Tuesday, June 11, 2013 6:34 AM
Points: 6,463, Visits: 1,388

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/alternativestosqlbackups.asp>http://www.sqlservercentral.com/columnists/awarren/alternativestosqlbackups.asp

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #7553

millennia

Posted Monday, October 28, 2002 3:22 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 02, 2011 11:29 AM
Points: 24, Visits: 12

Andy did add the caveat that MS Access is not the answer to all problems, but personally I am sick and tired of resolving locking problems on SQL Server caused by MS Access users on the databases. If I had my way every copy of Access in the building would be burned, and then we could have a query tool with a bit better idea about resource locking!

Post #44198

James Luetkehoelter

Posted Monday, October 28, 2002 6:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 06, 2011 8:20 PM
Points: 227, Visits: 33

Excellent article! It really brings out a very real issue that is overlooked by most -- backups don't protect you from everything. As you say at the end of the article, the job of the DBA is to protect the data and make sure it is available. In my experience user error is the biggest cause of data loss.

Another thing to consider is a policy-based solution. Who should have ad-hoc query access in the first place? Are developers truly testing and DML statements they are writing? What is *their* responsibility for data loss?

All to often I see an environment end-users and developers have the ability to change or delete data but no accountability for mistakes they may make to production or QA environments. I think it's the responsibility of the DBA to protect the data and ensure its availability, but I don't believe that this is an entirely *technical* issue.Policy needs to be set to establish guildlines for who has what level ad-hoc query access and to define accountability for altering or deleting data.

Two cents.

Post #44199

David.Poole

Posted Monday, October 28, 2002 6:44 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:13 PM
Points: 2,766, Visits: 1,442

The thought of having users who can update data using SQL statements scares me.
I don't mind them doing selects but I have serious issues with non-user interface updates of data.

I worked in an environment where the users needed to recode entries within a data warehouse. The recoding depended on the varying beliefs of the marketing department so it couldn't be built into a standard package.

Every month I took a snapshot of the main datawarehouse and replicated it to another server so the marketing department could do whatever they wanted to the data.

There was a procedure in place so that the person who cocked it up had to notify their manager, who would then have to sign off a restore. Sounds bureaucratic but in practice it was quick to operate.

The advantages were

Responsibility for said cock up was clearly visible
The manager of the department was responsible for saying "over-write all our work", or getting their staff to correct the error.
The main environment was protected.

Strangely enough, after implementing the policy the number of mistakes diminished dramatically.

LESSON:- Where possible make people responsible for clearing up their own mess.

LinkedIn Profile

Post #44200

Andy Warren

Posted Monday, October 28, 2002 7:54 AM

SSCertifiable

Group: Moderators
Last Login: Tuesday, June 11, 2013 6:34 AM
Points: 6,463, Visits: 1,388

Cant disagree that having users issuing direct updates is...well, less than optimal? In my case these users are reasonably well trained and are taught to be cautious. In last two years, less than a handful of incidents. The upside is that mgmt understands the risk. I still get stuck with fixing the problems though!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #44201

dan-16162

Posted Monday, October 28, 2002 9:44 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, January 14, 2013 12:49 PM
Points: 855, Visits: 91

Very good article. I actually have been using Access to back up SQL tables, and it has saved me A LOT of time. Andy's suggestion is very relevant to what I do: I develop for a small company that has a software product which uses an MS Access 2000 front end with a SQL Server 2000 back end. My focus has been on data processing, and I built a small form for backing up tables in Access where I can mark the checkbox next to any of 7 commonly backed up tables, type in the name of another table if I want, and enter a label for the backup. It backs up the tables I've chosen with the source table name, date, and label making up the name of the backup. This practice has been very helpful for me and has saved me a lot of time.

Post #44202

AFPeterson

Posted Monday, October 28, 2002 1:06 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 01, 2005 11:41 AM
Points: 258, Visits: 1

Andy, Keep pushing the limits! Gotta agree with you, there is more to data integrity than just nightly backups. As for solutions, as you have indicated, it all depends. Concurency issues, skill of users, sophistication of the application (audit tables), etc. does make a good case for purchasing Log Explorer.

What's the business problem you're trying to solve?

Post #44203

LordXmen

Posted Monday, October 28, 2002 1:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 08, 2003 12:00 AM
Points: 3, Visits: 1

I kinda agree with you that when it comes to databases it all comes down to who's to blame. It's almost usually the DBA cause the one who did the mistake would say, "He Gave me Access". That's why there shouldn't be any "power user" other than the actual DBA. If it all gonna come on you like 1000 bricks it might as well be "your" mistake.

Gerald Nelson MCP/MCDBA

Gerald Nelson MCP/MCDBA

Post #44204

Andy Warren

Posted Monday, October 28, 2002 5:53 PM

SSCertifiable

Group: Moderators
Last Login: Tuesday, June 11, 2013 6:34 AM
Points: 6,463, Visits: 1,388

Thanks to all who've posted. Pleasantly surprised by the reeeption, mentioning Access on a SQL site is usually a recipe for tar and feathers Smile

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #44205

wyattstafford1

Posted Friday, November 01, 2002 9:16 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, December 31, 2004 1:16 AM
Points: 12, Visits: 1

It is very helpful to me to "hear" a variety ideas about a variety of failure scenarios. Even if it is an idea that I don't adopt for whatever reason, it gets me thinking about the all the issues of keeping production systems running. My motto is "Murphy never sleeps." Very good article and hope for more contribution along these lines from the authors and other interested parties.

Post #44206

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions