April 10, 2012 at 6:45 pm
I am using SQL Server 2008R2 enterprise. We refresh our SQL database every night, during that time we don't want any body accessing that database.
So I am thinking of doing the following:
1. Disable all the logins except the one that is being used to refresh the database
2. After the database refresh is done, I will enable those logins back
Please let me know if there is a better way of doing this.
Thanks.
April 10, 2012 at 8:05 pm
Disabling all logins may be a little extreme... Depending on how you're refreshing you might be able to switch in and out of single-user mode to block access to everything except your process while it is running.
Josh Lewis
April 10, 2012 at 10:03 pm
If you are doing restore then obviously no one can access the database. its better to start the the refresh activity by keeping database in single user mode.
April 11, 2012 at 1:16 am
could always set the source DB to read_only instead of single_user then set it back to read_write once done
April 11, 2012 at 3:55 am
If you don't want anybody to make any updates, then making it read only should be good.
If you don't want even to read from data, then setting it to single user mode will solve your problem.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 11, 2012 at 4:48 am
Hi
I have some basic thing to say here, please correct me if i am wrong.
firstly while refreshing the database the process is
1) Backup the DB and then copy to the destination server and then restore it there .
now here while backing up the database as we start the backup the ceckpoint is issued and and backup is done till that point , user accessing the database will not effect the refresh process.
secondly while restoring the db no-body is allowed to touch the db untill and unless restore is totally complete.
so where does the user intervention come in this process????
April 12, 2012 at 2:49 pm
how about this for your restore:
alter database your_database_name set single_user with rollback immediate
go
alter database your_database_name set restricted_user
go
restore database your_database_name
go
alter database your_database_name set multi_user
go
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 12, 2012 at 3:51 pm
rudy komacsar - Doctor "X" (4/12/2012)
how about this for your restore:alter database your_database_name set single_user with rollback immediate
go
alter database your_database_name set restricted_user
go
restore database your_database_name
go
alter database your_database_name set multi_user
go
its what i do on one of our databases however i do not set restricted_user. i run the following as a sql agent job forgoing the go since its just a batch separator.
alter database your_database_name set single_user with rollback immediate
restore database your_database_name
alter database your_database_name set multi_user
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 9:08 am
alter database your_database_name set restricted_user
is kind of essential in our environment. it keeps out application servers and web servers that can actually jump in between the
alter database your_database_name set single_user with rollback immediate
and
restore database your_database_name
thus killing the restore !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply