SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore Issues - Database In use mode?


Restore Issues - Database In use mode?

Author
Message
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2484 Visits: 3465
HI.

Generally MS SQL SERVER 2008 R2 Entrprise Editon allowed for online restore process.. my case i am using same edition why it is not allowed online restore operation?

Database always says database in use and Exclusive access could not be obtained because the database is in use

Please confirm, any SPID blocked that process?
I have tried, set single user mode not help.. and kill SPID that praticular database but not help..

Thanks
ananda
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10232 Visits: 13687
presuming this is a user database you are trying to restore?

run select * from master..sysprocesses where db_name(dbid) = 'your database'

kill any spids this returns and run our restore (in the same batch)

If the connections keep getting to the database before you can run

alter database your database set offline with rollback immediate;

then your restore in the same batch

---------------------------------------------------------------------
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84623 Visits: 41067
First of all, I strongly recommend against killing SPIDs for anything ever. It's a documented problem on CONNECT that the KILL process can leave spids that consume large amounts of resources trying to do a "zero rollback" that will never finish because there's actually nothing to rollback. To date, the only fix for such "zero rollback" spids is to bounce the SQL Server Service. I have witnessed/been victim to this problem myself.

Instead of killing spids, write a script to set the database to single user mode with ROLLBACK IMMEDIATE, immediately change it back to multi-user mode (in case you lose the connection, you don't want something else grabbing the only connection available), and immediately start the restore all within the same script. The attached Webservices and the like won't be fast enough to do a reconnect before the restore starts if you do those 3 steps in a single T-SQL script (I do this all the time on my Dev and Staging boxes when doing restores/refreshes). Don't forget, your SSMS session must not be attached to the database while running the script.

I also don't take the database offline unless there are other compelling reasons to do so. I let the restore do that.

Second, the question is about ONLINE restores. That would imply NOT killing any connections. I believe the real answer here is that you can't do an "online" restore if the only filegroup available is the PRIMARY filegroup. In fact, according to what's in Books Online ("restores, online" in 2005 and "Piecemeal" restores in 2005 and up), I don't believe you can do an "online" restore of the PRIMARY FG even if multiple other FGs exist because the PRIMARY FG must be up and fully functional at all times for the database to be "online" to begin with.

All that being said, I strongly recommend you spend some time on "restores" in Books Online before you try anything else. To do otherwise is like a blind man smoking a cigarette in a gunpowder factory. ;-) And, just to be sure, "Books Online" is the help system you can get to by pressing the {f1} key while in SSMS.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
free_mascot
free_mascot
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3951 Visits: 2235
Hello Anand,

Looks like you would like to do Online restore and you are doing normal database restore. Go through the following link which will help you to understand the online restore.

http://technet.microsoft.com/en-us/library/ms188671.aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search