Sql Server database Read_Only / Read_Write

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.

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

Make Database Read Only

USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT
GO

Make Database Read/Write

USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT
GO







In case you get the following error message make the database single user:

Msg 5070, Level 16, State 2, Line 1

Database state cannot be changed while other users are using the database 'TESTDB'

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.




Execute the following query to make the database single user:




ALTER DATABASE [TESTDB] 

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Result:

--Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

To Rename the database:

ALTER DATABASE [database1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
exec sp_renamedb "database1", "database2"
ALTER DATABASE [database2] SET MULTI_USER