Restoring database keep getting error session in use

Question

Post reply

Restoring database keep getting error session in use

Aubergine Blue

SSChasing Mays

Points: 656
More actions
March 22, 2006 at 10:41 am

#168188

Full error:
' RESTORE cannot process database 'SmecIntranet' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo) '
I know it seems obvious enough, session in use, but I ran sp_who2 and there are no processes running against the db I want to restore. I have tried clicking on Master db and restoring from there, but still get same error.
I also checked the processes in the Activity Monitor part and there is nothing
I have restored this db once before when I created it and it was OK.
The backup comes from a SQL2000 server.
I am a bit green with 2005, am I missing something completely obvious?
March 27, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

Pierre Portenier Valued Member Points: 61 More actions · Answer 1

Hello,

I have had the same problem and don't understand why...

As bypass, I have deleted the database, recreated it and after that the restore has worked. It is not glorious... but it works...

Pierre

Aubergine Blue SSChasing Mays Points: 656 More actions · Answer 2

Many Thanks

I eventually tucked into the SQL query analyser side of it, and started to write a restore script, took a bit of fiddling, but this went thru eventually OK:-

RESTORE DATABASE SmegIntranet

FROM DISK = 'C:\SmegIntranet_db_200603220900.BAK'

WITH NORECOVERY,

MOVE 'SmegIntranet_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SmegIntranet_Data.MDF',

MOVE 'SmegIntranet_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SmegIntranet_Log.LDF',

replace

GO

Blimey SQL 2005, takes so much care of itself, almost makes it more difficult to do what you could do easily before.

Thanks

Sriram l Grasshopper Points: 21 More actions · Answer 3

HI,

I faced the same error message while trying to restore by rewriting an existing database. I solved this by selecting the databse insql server studio and then clicking on make offline which takes the database offline. Select the correct mdf and ldf files and bingo it works !!!

Regards,

Sriram

Val-159407 Valued Member Points: 59 More actions · Answer 4

If you do not see any active sessions for the database you are trying to restore, it means that this is the default db of the user you logged in as. The db gets locked and you won't be able to restore it.

To avoid this effect, log in as some other user or change default db to master.

Jules SSC Eights! Points: 977 More actions · Answer 5

I know this is adding to a really old post but that's exactly the message I'm getting, trying to restore a test version of a database from last night's backup of the live version. Both in sqlserver 2005. My default database when I log into the server is tempdb, so any advice gratefully received.

Cheers, Jules.

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

Please start a new thread for a new question in future.

There's some connection to the DB that you're trying to restore. Not necessarily yours, but there is one. If you're using management studio, it could be the connection from object explorer.

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