At my work, I had to overwrite a MSSQL database while in production. The goal was to replace the existing database by one with a new structure.

I tried first the easy way, right clicking on it and trying to take it off line using the menu of Microsoft.

Unfortunately, that didn’t do anything, complaining that several users were accessing it. Of course, trying to restore it directly gave the same message.

I couldn’t find a way to successfully force it through the interface.

It was, of course, a multiuser database on which always an user was working on, making it impossible to overwrite it.

So, at this point, I had to consider using the SQL query tool, to enjoy the power of a command line interface (you see what I mean).

Below is the kind of request I submited :

use master;
alter database $your_database set single_user with rollback immediate;
backup database $your_database to disk = 'C:\$your_database.bak' with init,Name = 'backup';
restore database $your_database from disk = 'C:\path\to\your\DB\$your_new_database.bak' with replace,file=1,recovery
alter database Datasharing_Translation set multi_user with rollback immediate;

It switches the database to the single user mode, make a backup of it (for safety), make the restoring operation, and finally switches it back to the multi user mode.

Once again the day is saved by a command line tool ! ;)