MS SQL Server Concepts and Programming Question: Download Questions PDF
How to set database to be READ_ONLY in MS SQL Server?
Answer:
Databases in SQL Server have two update options:
* READ_WRITE - Data objects are allowed to be queried and modified. This is the default.
* READ_ONLY - Data objects are allowed to be queried, but not allowed to be modified.
You can use the "ALTER DATABASE" to change database update options as shown in the tutorial below:
USE GlobalGuideLineDB
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
GO
(1 rows affected)
ALTER DATABASE GlobalGuideLineDB SET READ_ONLY
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
GO
Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1
Failed to update database "GlobalGuideLineDB" because
the database is read-only.
SELECT * FROM Links
GO
Name
www.globalguideline.com
ALTER DATABASE GlobalGuideLineDB SET READ_WRITE
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
GO
(1 rows affected)
As you can see from the output, inserting data into a table is not allowed if the database is in READ_ONLY mode.
* READ_WRITE - Data objects are allowed to be queried and modified. This is the default.
* READ_ONLY - Data objects are allowed to be queried, but not allowed to be modified.
You can use the "ALTER DATABASE" to change database update options as shown in the tutorial below:
USE GlobalGuideLineDB
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
GO
(1 rows affected)
ALTER DATABASE GlobalGuideLineDB SET READ_ONLY
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
GO
Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1
Failed to update database "GlobalGuideLineDB" because
the database is read-only.
SELECT * FROM Links
GO
Name
www.globalguideline.com
ALTER DATABASE GlobalGuideLineDB SET READ_WRITE
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
GO
(1 rows affected)
As you can see from the output, inserting data into a table is not allowed if the database is in READ_ONLY mode.
Download MS SQL Server Interview Questions And Answers
PDF
Previous Question | Next Question |
How to move database physical files in MS SQL Server? | How to set database to be SINGLE_USER in MS SQL Server? |