July 14, 2008 at 6:37 am
Hi,
While restoring database in sql server 2005, i am getting hte following error in sql server 2005
"Exclusive access could not be obtained because the database is in use "
No users are connected except me as dba.
Pls help me.
July 14, 2008 at 7:22 am
It's your connection it's complaining about. To restore, there must be no users connected to the DB
Change to another database (master) before doing the restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2008 at 7:37 am
Change to another database (master) before doing the restore. ?
i am not getting exactly wat u r trying to tell
July 14, 2008 at 7:41 am
Make sure you are not connected to the database you are trying to restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2008 at 7:48 am
Change to another database (master) before doing the restore. ?
i am not getting exactly wat u r trying to tell
USE master
GO
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\BackupFileName.BAK'
July 14, 2008 at 9:22 am
i did as u told
i thought of changing to single user and restore can i use the below one
alter database DM_Global set SINGLE_USER
July 14, 2008 at 10:36 am
Dear Ashok,
See whether any users are connected to the database that you are restoring, if so ask them to change the database context or close their connections.
Query for you to find out who the users are accessing the database
select spid,loginame,program_name,hostname from master..sysprocesses
where dbid = db_id(' '
July 15, 2008 at 6:40 am
hi murali,
i ran the query
select spid,loginame,program_name,hostname from master..sysprocesses
where dbid = db_id(' '
no body is connected
but still i am getting the error wat to do?pls help
July 15, 2008 at 7:33 am
Hi, use the master database and run the command sp_who2 to check who is accessing the database that you are trying to restore.
check for the spid here against the database.(blkby column) if found any kill it.
the use the master database to restore. You can stop then start server and then do the restore;) this would do!!
July 15, 2008 at 7:41 am
Hi ruin,
i can stop and start the server,without doing this i should do it.
July 15, 2008 at 7:51 am
So you can restore the database by stopping and starting the server?
Else you need to check for the SPIDs that are blocking the database. Use the master database to restore.
Restore command :
restore database DBname from disk = 'Z:\.BAK'
with move 'datafilename_Data' to ' ',
move 'logfilename_Log' to ' ',
replace
hope this helps:P
July 15, 2008 at 9:05 am
i stopped and started the services,but still same error
in restore command wats the use of Move statement.
i checked spid no users are connected.
July 15, 2008 at 9:17 am
could you please paste the error that you are getting now?
move syntax -> moves the data and log files to their respective drives, this is the case when you get a backup file from another server and then do a restore, since the mdf and ldf file location would have changed. thats why we use a move syntax;)
July 15, 2008 at 9:22 am
Exclusive access could not be obtained because the database is in use
this is the error i am getting
if i do manullay using managment studio same error.:w00t:
July 15, 2008 at 9:29 am
Set the database offline before you start the restore. That will prevent users from connecting to it.
use master
alter database MyDatabase set offline with rollback immediate
restore database MyDatabase...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply