May 21, 2009 at 12:34 pm
Is there a command or process that I can invoke that will keep users that are not DBOs from accessing any data on a SQL Server or database?
I am going to be backing up databases on our 2000 box to be restored on our new 2005 box. I don't want anyone accesssing data during the backup. A couple of my databases are so large the backup takes about an hour.
Lonnie Johnson
May God Bless You Beyond Your Imagination
Prodev
May 21, 2009 at 12:40 pm
You can run
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
before the backup and then run
ALTER DATABASE [DatabaseName] SET MULTI_USER
after the backup completes
May 21, 2009 at 12:49 pm
I like to use RESTRICTED_USER rather than SINGLE_USER to make sure I'm the one who can connect to the database.
Greg
May 21, 2009 at 12:50 pm
You should put the statement before the backup command. Then the backup command will have full control of the database. In your case you may want to use RESTRICTED_USER instead.
SINGLE_USER | RESTRICTED_USER | MULTI_USER
Controls which users may access the database. When SINGLE_USER is specified, only one user at a time can access the database. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. MULTI_USER returns the database to its normal operating state.
May 21, 2009 at 1:19 pm
You could set the db to read only as well. That won't prevent the backup, or access by users, but it will stop them from altering data.
May 21, 2009 at 2:05 pm
Ok. I went with the SET RESTRICTED_USER.
I will have three other people helping with other task as we migrate. They all have dbo access.
However, I ran it on our test server which has the same number of production databases and it seems to hang up on databases that are in use. Now these databases were also large.
My question is do the users have to be out or does it take a long time for db's that are large. Also the developers that were testing programs on the test server said that they started getting weird messages about accessability.
Lonnie Johnson
May God Bless You Beyond Your Imagination
Prodev
May 21, 2009 at 7:44 pm
The change in the state of the database doesn't occur until there are no locks of any type in the database. You need to include the "WITH ROLLBACK IMMEDIATE" clause to immediately rollback any active transactions.
If that clause is omitted it allows any in-flight transactions to complete and users who are currently connected can execute new commands.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply