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

  • 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[/url] 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...

    😀

    Thanks,

    Jason A.

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

  • 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

  • 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, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

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