How to: Restore a SQL Server database with TSQL

The task of restoring a SQL Server database with a TSQL script is as simple as executing the following script:

RESTORE DATABASE [MyDatabase]
FROM DISK = N'C:\Backup\MyDatabase.bak'
WITH  FILE = 1,
MOVE N'MyDataLogicalName' TO N'D:\DatabasesData\MyDatabase.mdf', 
MOVE N'MyLogLogicalName' TO N'D:\DatabasesLog\MyDatabase_log.ldf', 

The only problem with the previous script is that if there are any open connections to the database the following error is generated:

Exclusive access could not be obtained because the database is in use.

To fix the issue one solution is to put the database in single user mode before the backup.

-- Put the database in single user Mode
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

-- Restore the Database
RESTORE DATABASE MyDatabase FROM DISK ...

-- Put the database back in multiuser mode
ALTER DATABASE MyDatabase SET MULTI_USER
GO

If this script fails, execute the last statement to put the data base back in multiuser mode.

To simply restore the database use:
-- Put the database in single user Mode
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
-- Restore the Database
RESTORE DATABASE MyDatabase FROM DISK = N'C:\Backup\MyDatabase .bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
-- Put the database back in multiuser mode
ALTER DATABASE MyDatabase SET MULTI_USER
GO