database in use error while restoration

[hritabrata_r]

pls tell me how to solve the database in use problem while restoring database using query.

[rmiao]

Have to disconnect all users from target db, and run restore statement under any db other than target db.

[Rawhide]

I usually do the following all in one transaction. If you run it one step at a time it won't always work. I'll explain why after the example.

Use <Database Name>

Alter Database <Database Name> Set Single_User With Rollback Immediate

Restore Database <Database Name>
From Disk = 'D:\Path\To\BackupFile.bak'
With Replace

Alter Database <Database Name> Set Multi_User




Now the reason it will not always work if you run it in multiple steps is because someone else can grab the single connection while you are not using it. When you put it in single user mode while connected to it, it rolls back and kills every connection but your own. By running the transaction all at once, it ensures that when you execute the lines following the first alter statement, you are using the only connection to the database.