How Restore backup as overwrite on a database in use in SQL Server?

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply