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