Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Reg:Sql server database readonly

Author  Topic 

ycr1988
Starting Member

3 Posts

Posted - 2013-06-26 : 10:10:37
Hi Experts,

I have a requirement like the below steps,
1)I want to lock the database (as it readonly).
2)I will add tables ,add columns to existing tables or make some columns contains default values.
3)Then i want release database.

Regards,
Chakradhar.


Ycr

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-26 : 10:17:40
I can think of two ways, there may be a better way perhpas:

1. Put the database into single user mode (http://msdn.microsoft.com/en-us/library/ms345598.aspx), make your changes, and then set it back to multi-user mode. The disdvantage is that other users won't even be able to connect to the database during that period, and it will require disconnecting any others who are already connected. So probably not a good solution.

2. Write your update queries, and at the beginning of the query, take an exclusive lock on all the tables that you plan to touch, and release the lock at the end of the query. If the queries are of short duration, the clients should not even notice that you locked them. The disdvantage is that you will need to be careful to make sure that you lockdown everything you want to lockdown. http://msdn.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
Go to Top of Page
   

- Advertisement -