1

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

4
  • 1
    why not use ssms? – nbk Apr 18 at 22:06
  • What have you tried so far, what errors are you seeing? – HandyD Apr 19 at 5:46
  • I Want to use query because of my program in C# – Mojtabah Apr 19 at 6:36
  • Can you help me about this query I want it ? – Mojtabah Apr 19 at 6:37
2

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
1
  • Thanks so much ♥ – Mojtabah Apr 19 at 10:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.