Can we restore the database forcefully when it is in use via SQL script?

 Posted by Virendradugar on 4/19/2010 | Category: Sql Server Interview questions | Views: 8040
Answer:

When you try to restore the database (RESTORE DATABASE) through command and if it's in use then SQL SERVER will not allow you to do so. It will throw an error.

But there is a way to restore database forcefully. But there are chances of data loss.

To restore database forcefully, one need to bring it in SINGLE USER mode. and then restore it and again put the database in MULTI USER mode. Make sure, you are always using MASTER database for this operation.

See below script.

--To Restore forcefully to an existing database which is in use.

Use Master;
ALTER DATABASE virendra SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE virendra FROM DISK='D:\virendra.bak' WITH REPLACE
ALTER DATABASE virendra SET MULTI_USER


Thanks,
Virendra Dugar


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response