CODEWITHSUNDEEP
sql-serverlocking
user1088045
user1088045

Reputation: 294

How can I lock an SQL Server database for changes?

I want to make sure my database (SQL Server 2008) remains immutable, but still accessible for reads during some maintenance operations (backups, transitions to other DB servers etc), how can I programmatically lock it for changes in C#?

Upvotes: 8

Views: 16891

Answers (1)

Filip De Vos
Filip De Vos

Reputation: 11908

You can execute an ALTER DATABASE <mydb> SET READ_ONLY to put a database in read-only mode.

If you want the command to fail if it can't be executed immediately you specify it like this:

    ALTER DATABASE <mydb> 
    SET READ_ONLY
    WITH NO_WAIT

If you want to make it rollback all open connections you specify the command like this:

    ALTER DATABASE <mydb> 
    SET READ_ONLY
    WITH ROLLBACK IMMEDIATE

These options are well documented in SQL Server Books online

Upvotes: 15

Related Questions