Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : Read only databases

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.67k
    Comment on it

    Databases whose data is not required to be changed should be considered to be set as READ ONLY.Databases can be set to READ ONLY mode and back using T-SQL and SSMS.

    Following are the scripts that can be used to set database read only and back:

    ---Make Database Read Only

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

    ---Make Database Read/Write

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

    Once a database is changed to READ ONLY nothing can change in it.So certain changes should be made to optimize the performance of a READ ONLY database.

    1) Permissions cannot be edited from a READ ONLY database.

    2) Users cannot be added or removed from a READ ONLY database

    3) Statistics will not be automatically updated (not required) and you would not be able to update statistics of a READ ONLY database

    4) READ ONLY databases will not shrink automatically neither manually

    5) You cannot create indexes on a READ ONLY database.

    6) You cannot defragment indexes of a READ ONLY database

    Hence we should complete such tasks before we set the database to READ ONLY mode.

    We can verify the READ ONLY state of database by using the below query where a value of 1 corresponds to READ ONLY state.

    SELECT name, is_read_only 
    FROM sys.databases 
    WHERE name = 'DBName'
    GO
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: