Restore MSSQL Database

2017-01-06

Kill active sessions

Firstly, it would be better to stop all the applications/services which are connecting to the Database which needs a restore.
Then, kill the active sessions as this will block the restore process if there are any active sessions.

use master;
declare @kill varchar(max) = '';
select @kill = @kill + 'kill ' + convert(varchar(10),S.session_id) + ';' from sys.dm_exec_sessions S 
join master..sysprocesses P on P.spid = S.session_id
where P.dbid=db_id('DBTest') and S.session_id != @@SPID and S.is_user_process = 1;
exec (@kill);

Restore DB

USE master
RESTORE DATABASE DBTest 
FROM  DISK = N'c:\DBTest.bak' 
WITH  FILE = 1,  
MOVE N'DBTest_Data' TO N'c:\DBTest_Data.mdf',  
MOVE N'DBTest_Log' TO N'c:\DBTest_Log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10

Change user

USE DBTest
EXEC sp_change_users_login 'Update_One', 'testuser', 'testuser'