Restrict insert or update when taking backup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Restrict insert or update when taking backup

Restrict insert or update when taking backup

Rate Topic

Display Mode

Topic Options

Author

Message

rajkiran.panchagiri

Posted Thursday, December 13, 2012 2:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 4:15 PM
Points: 39, Visits: 402

Hi,

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

Thanks in advance..

Post #1396056

anthony.green

Posted Thursday, December 13, 2012 2:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1396057

rajkiran.panchagiri

Posted Thursday, December 13, 2012 2:45 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 4:15 PM
Points: 39, Visits: 402

Is there any other way other than this?

Post #1396069

anthony.green

Posted Thursday, December 13, 2012 2:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Post #1396073

sankar276

Posted Thursday, December 13, 2012 2:59 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:05 PM
Points: 99, Visits: 98

Policy based management.

Post #1396075

anthony.green

Posted Thursday, December 13, 2012 3:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

sankar276 (12/13/2012)

Policy based management.

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

Post #1396086

GilaMonster

Posted Thursday, December 13, 2012 3:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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 2008, MVP
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

Post #1396095

Jeff Moden

Posted Thursday, December 13, 2012 8:41 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1396466

Joy Smith San

Posted Thursday, December 13, 2012 9:53 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 1,371, Visits: 2,302

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.

Post #1396474

« Prev Topic | Next Topic »

Permissions