Making
a SQL Server Database Read-Only
There are
many situations where it is important that users are unable to modify the
contents of a database. For example, if data is to be migrated to another
server or for reporting purposes. This tip explains how to set a database to a
read-only mode.
ALTER DATABASE Command
The ALTER DATABASE command allows a database
administrator to modify SQL Server databases and their files and filegroups.
This includes permitting the changing of database configuration options.
Setting a Database to
Read-Only
When you need to ensure that the data is a
database is not modified by any users or automated processes, it is useful to
set the database into a read-only mode. Once read-only, the data can be read
normally but any attempts to create, updated or delete table rows is
disallowed. This makes the read-only mode ideal when preparing for data
migration, performing data integrity checking or when the data is only required
for historical reporting purposes.
To make a database read-only, the following
command is used:
ALTER DATABASE database-name SET READ_ONLY
Setting a Database to
Read-Write
If the read-only requirements for the database
are temporary, you will need to reset the configuration option following any
procedures undertaken. This is achieved with a small modification to the ALTER
DATABASE statement to indicate that the database should return to a writeable
mode.
ALTER DATABASE database-name SET READ_WRITE
No comments:
Post a Comment