May 12, 2011 at 6:40 am
I have a few old 2000 databases that I would like to move off the local C: Drive and onto the new G: Drive. I practiced this on a database that had no users connected to it by Detaching...logging into the server and physically moving the detached databases mdf and ldf to their new location on G:. I then re-attached making sure the correct DBO was selected which it was not by default. I then scripted the command out to the query windows and everything looked good and ran it successfully. I then refreshed and saw the database had been re-attached properly.
Now my question is...
I ran sp_who2 and I can see that I have a particular login who is sleeping awaiting command and has several SPIDs to a database I need to move. Is it safe from me to go ahead and detach - re-attach this database?
Thanks!
May 12, 2011 at 6:58 am
As far as I know, it won't let you detach the database if there are any open connections to it. You'll have to kill the processes before detaching the database. No one here can tell you how safe it is, because no one here knows what those processes are doing in the database.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 6:59 am
I read that I should consider putting the database into single user mode before performing the maintenance on it. So now I am wondering since the users is just an application, will it automatically reconnect to the database after I put it back into multi-user mode?
May 12, 2011 at 7:03 am
Adi,
The logins program name is 'xxxxx database application' and it has several different host names ie room1, room4, room6. I am not familiar with the application so how would I figure out what each of these connections is doing? That would take forever.
May 12, 2011 at 7:53 am
You do have to kick out all those connected users first and foremost:
alter database databasename set offline with rollback immediate
alter database databasename set online
exec sp_detach_db databasename
Bazinga!
May 12, 2011 at 1:08 pm
sqlapprentice (5/12/2011)
You do have to kick out all those connected users first and foremost:
alter database databasename set offline with rollback immediate
alter database databasename set online
exec sp_detach_db databasename
If I kick out all those connected users and I detach and re-attach will they become orphans?
May 12, 2011 at 1:22 pm
Much better to set to single user mode.
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
No, users will not become "orphans" by kicking them off the DB. They just lose their current connection. Whether or not they automatically reconnect is an application thing, not a database thing.
May 12, 2011 at 1:25 pm
Pam Brisjar (5/12/2011)
Much better to set to single user mode.
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATENo, users will not become "orphans" by kicking them off the DB. They just lose their current connection. Whether or not they automatically reconnect is an application thing, not a database thing.
Excellent! Thank you!!!
May 12, 2011 at 1:56 pm
You will need to do the detach from the same query window.
Make sure it returns to multi-user when you reattach.
May 12, 2011 at 2:02 pm
if its in single user mode how do you know you will be the fist user to connect to the database?
rather than detach\attach use alter database modify file command. It will avoid a change in database owner, changing the dbid which could affect default database settings.
Preferably get the application connecting to the database stopped, at least arrange an outage and let the users know.
then:
backup the database (final tranlog or diff if you want)
alter database dbname set offline (with rollback immediate if required)
alter database modify file command
COPY the files to the new location
alter database dbname set online
once you are sure everything is OK you can delete the old files - and backup the database
---------------------------------------------------------------------
May 12, 2011 at 2:48 pm
If you have an open query window to the DB from which you issue the ALTER DATABASE command, that open query window is the single user.
Check it out:
USE DBA_DB
ALTER DATABASE DBA_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
SELECT * FROM dbo.MyTable as mt
ALTER DATABASE DBA_DB SET MULTI_USER
May 12, 2011 at 3:08 pm
our posts crossed pam, that was not explicit from your first post 🙂
alter database modify file is still the better way to move database files, which means offlining the database.
---------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply