Post reply

Restrict insert or update when taking backup

  • December 13, 2012 at 2:10 am

    #271629

    Hi,

    Is there any way to restrict insert or update or delete operation while taking backup..

    Thanks in advance..

  • December 13, 2012 at 2:16 am

    #1568200

    Set the database to read only,

    Deny insert update delete to the users, then re-enable it (only works if security is managed properly and the accounts dont have elevated rights)

    Set database to single user then back to multi user

    But the question comes down to why do you want to do this?

  • December 13, 2012 at 2:45 am

    #1568210

    Is there any other way other than this?

  • December 13, 2012 at 2:46 am

    #1568214

    Detach the database and copy the mdf/ndf/ldf's some where else

  • December 13, 2012 at 2:59 am

    #1568215

    Policy based management.

  • December 13, 2012 at 3:18 am

    #1568225

    sankar276 (12/13/2012)

    Policy based management.

    Can you detail how PBM will stop insert updates and deletes happening to a DB?

  • December 13, 2012 at 3:54 am

    #1568233

    rajkiran.panchagiri (12/13/2012)

    Is there any other way other than this?

    Not really. Setting the DB to read only or single user for the duration of the backup is the simplest. The question is why you want to prevent data changes during a backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 13, 2012 at 8:41 pm

    #1568542

    GilaMonster (12/13/2012)

    rajkiran.panchagiri (12/13/2012)

    Is there any other way other than this?

    Not really. Setting the DB to read only or single user for the duration of the backup is the simplest. The question is why you want to prevent data changes during a backup.

    I agree with Gail. Why would you want to restrict access during a backup?

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

  • December 13, 2012 at 9:53 pm

    #1568549

    A part of the log will also be backed up at the end of the backup process which helps SQL server to rollback or roll forward. Why should you worry about the transactions that happen during the backups ? Experts, pealse correct me if am wrong.

Viewing 9 posts - 1 through 8 (of 8 total)

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