CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2000
m0dest0
m0dest0

Reputation: 859

SQL Server - Keep synchronized a read-only database

I need to keep synchronized a couple of databases, one is on sql 2000 and the other on 2005. The one on 2000 should be kept in read-only mode to make sure the users does not enter data. The 2005 is the one which are going to be updated by the users. So I could develop a script to truncate and insert into the 2000 version with data from 2005 every night. My question is if there is some way to disable the read-only mode temporaly while the script is running. Is there a better approach? Thanks,

Upvotes: 1

Views: 1417

Answers (3)

TDrudge
TDrudge

Reputation: 767

I would tend along the response offered by Mike Henderson. Essentially leverage db security to prevent users from modifying data and allow the account used to synchronize data to write.

Not sure what your time constraints are, but if you are relying on the db to be read-only to prevent end-users making changes, there is the possibility that they could get in during the time period that the db is in read/write mode.

Upvotes: 1

m0dest0
m0dest0

Reputation: 859

Just for future reference, this is how I proceeded, 

ALTER DATABASE MyDB SET READ_WRITE WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDB SET READ_ONLY WITH ROLLBACK IMMEDIATE

Thank you,

Upvotes: 0

Mike Henderson
Mike Henderson

Reputation: 1317

You can disable the read-only mode while the script is running. You may also want to set RESTRICTED_USER to keep any users from accessing the database while processing.

Upvotes: 2

Related Questions