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.
1 2 3 4 5 6 |
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
1 2 3 4 5 6 7 |
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
1 2 |
USE DBTest EXEC sp_change_users_login 'Update_One', 'testuser', 'testuser' |