How Restore backup as overwrite on a database in use in SQL Server? I Want to restore a backup with overwriting on a database in use
for this problem I want to create a query base on these steps :
1- Remove All Connections [MyDB]
2- Create a backup from [MyDB]
3- Restore a Backup as an Overwrite on [MyDB] from specifying a path
Thank you very much for your help
You can kill all connections this way:
USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

Then you pass to single user mkode:
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK AFTER 60 --this will give your current connections 60 seconds to complete

Then we create a backup:
BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
GO

Then we replace the database:
--Do Actual Restore
Restore Database [MyDatabase] From Disk = 'D:\Path\to\Backup.bak'
With Replace,
Move 'Data file logical name' To 'New physical path and name.mdf',
Move 'Log file logical name' To 'New physical path and name.ldf'';

And then we go back to multi user mode:
/*If there is no error in statement before database will be in multiuser
mode.  If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO