DATABASE RESTORE

Question

Post reply

DATABASE RESTORE

kapfundestanley

SSCertifiable

Points: 5637
More actions
January 26, 2012 at 2:55 am

#254239

I have a database on my test server,each time i want to restore on it,it always complains about exclusive access,i end up restarting the server in order to restore even after killing processes and stopping all the services connecting to it.What could be the problem with this database on this server.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I guess this is a RESTORE WITH REPLACE, isn't it?

Just DROP the database before restoring, no need to restart the instance.

-- Gianluca Sartori

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 2

Yes it's with replace but drop fails it says database is in use.If dropped fine,can I continue dropping a database each time I want to restore I can restore other databases without this problem.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You have to identify the sessions using the database and kill them.

The easiest way is querying sys.sysprocesses (I know, it's a compatibility view, but does the trick very fast).

SELECT spid

FROM sys.sysprocesses

WHERE DB_NAME(dbid) = 'NameOfDatabaseToDrop'

-- Gianluca Sartori

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 4

OK,I CAN LIST ALL THE PROCESSES AND KILL THEM EXCEPT ONE WHICH SAYS CAN NOT KILL OWN PROCESS.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Then, it's the session that runs the KILL statement, your current session in SSMS.

Issue a "USE master" command and you should be fine.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

That's your own connection. You're blocking the restore with your own session, make sure you're connected to a database other than the one you are restoring over.

p.s. Please don't type in all caps, it's the equivalent of shouting at us.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 7

Thank you.Sorry for the caps.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris