Possible to use Policies to force ad-hoc backups to be copy

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Possible to use Policies to force ad-hoc...

Possible to use Policies to force ad-hoc backups to be copy_only?

Rate Topic

Display Mode

Topic Options

Author

Message

jasona.work

Posted Friday, March 08, 2013 1:14 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 472, Visits: 2,374

So at my new employer, the backup schedule is weekly fulls, daily differentials, and bi-hourly transaction logs.

We need to fairly often (couple times a week or so) take backups of production DBs and restore them to QA. Having read this blog posting from another topic on this here, I can see we should do copy_only so as to not break the differential sequence.

I took a brief look into Management Policies, and to be honest, I'm thinking the answer to this is going to be that we can't enforce this, we'll have to make sure to do it ourselves.

But it doesn't hurt to ask...
BigGrin

Thanks,
Jason A.

Post #1428747

Michael Valentine Jones

Posted Friday, March 08, 2013 10:08 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517

I suggest not giving people permissions to do backups, ands setup a job they can execute that does the copy only backup.

Post #1428849

jasona.work

Posted Saturday, March 09, 2013 6:07 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 472, Visits: 2,374

I had actually thought about something along those lines, although I was thinking of doing a stored procedure that they could call.

Maybe set it up to allow them to choose the directory and the DB to backup to.

At the moment, I'm still sort of feeling my way into the job, finding out just how much I can or cannot do with the servers (the systems are very firmly locked down, although not so far that I haven't been able to do my job yet.)

There's a fairly solid separation of duties, too.
There's a person responsible for configuring the backup application used (CommVault) who is quite easy to work with as far as setting up schedules for the backups (and knows enough about SQL to understand WHY the TLog needs to be backed up)
There's a couple people responsible for adding / removing / changing users (including service accounts) in the domain (again, fairly easy to work with)
There's my "partner-in-crime" who's responsible for the Oracle systems, and who I am supposed to be cross-training / being cross-trained by.
Then there's the developer who was covering when the previous DBA left.

Whew.
Jason

Post #1428944

SQLRNNR

Posted Saturday, March 09, 2013 8:43 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

Michael Valentine Jones (3/8/2013)

I suggest not giving people permissions to do backups, ands setup a job they can execute that does the copy only backup.

This would be my inclination as well.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1428950

« Prev Topic | Next Topic »

Permissions