restore a database while force the exisitng connection to the database to be close before restore.

dear all,

right now we are trying to restore a DB backup and overwrite the existing one, but whenever we see there are connection to the database, the restore doesn't work, it is so weird that it happens and I didn't try it before.

the backup option is :

RESTORE DATABASE [xxx]
FROM  DISK = N'C:\temp\yyy\xxxVA_02.BAK'
WITH  FILE = 1,
MOVE N'VMSVA_02' TO N'D:\Database\MDF\xxxVA_02.mdf',  
MOVE N'VMSVA_02_Indexes' TO N'D:\Database\MDF\xxxVA_02_Indexes.ndf',  
MOVE N'VMSVA_02_Audit' TO N'D:\Database\MDF\xxxVA_02_Audit.ndf',  
MOVE N'Partition_1' TO N'D:\Database\MDF\Partition_1.ndf',  
MOVE N'Partition_2' TO N'D:\Database\MDF\Partition_2.ndf',  
MOVE N'Partition_3' TO N'D:\Database\MDF\Partition_3.ndf',  
MOVE N'Partition_4' TO N'D:\Database\MDF\Partition_4.ndf.ndf',  
MOVE N'Partition_5' TO N'D:\Database\MDF\Partition_5.ndf.ndf',  
MOVE N'VMSVA_02_log' TO N'D:\Database\LDF\xxxVA_02_log.ldf',  
 NOUNLOAD,  REPLACE,  
 STATS = 10
GO

any way to disable/kill the existing DB connection and let the restore works well ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David SankovskySenior SysAdminCommented:
Try this:

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK AFTER 60 --this will give your current connections 60 seconds to complete

--Do Actual Restore using the code you already wrote

/*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

Open in new window


Or, alternatively, Try this:

use master;

 

alter database <YourDB>

       set offline with rollback immediate

 
--Do Actual Restore using the code you already wrote
 

alter database <YourDB>

       set online with rollback immediate
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
ok, so to you, when DB has connection, we can't restore over it ?
0
David SankovskySenior SysAdminCommented:
When there are connections, people might try to write to it, which will cause the restore to fail. So yes, it's preferable to kill all connections to the DB before restoring it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
but MS SQL server should still allow to overwrite,100% no ?
0
David SankovskySenior SysAdminCommented:
Not exactly.
Restoring isn't exactly writing over tables, the background process is slightly different, it is therefor highly recommended to either disconnect all users from the database for the duration of the restore or take it down all together (the 2nd snippet I sent)

Trying to force a restore with connected users can cause even more damage than there is right now.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Trying to force a restore with connected users can cause even more damage than there is right now. "

e.g, data corrupt ?
0
David SankovskySenior SysAdminCommented:
Mainly Yes, but also main system halts (which can in some cases result in corruption in other DBs as well.

I don't understand what you are getting at though.. You've asked for a way to kill existing connections to a Database.

I gave you two options to do so and now you are trying to avoid it while I keep telling you that the best practice IS to disconnect all active connections.
0
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, ok , just try to figure more in detail. tks.
0
marrowyungSenior Technical architecture (Data)Author Commented:
probably will have chance to test but I don't wish to as it also means the restore are in trouble again.

will come back even after this ticket close if I really need to but I don't want.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't restore on a database that is in use since the data and log files are locked by the running processes and that's why you should disconnect all connected users before performing a restore.
0
marrowyungSenior Technical architecture (Data)Author Commented:
so the solution above is good to close all connection, anything you can suggest ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, close all connections is the only possibly suggestion.
0
marrowyungSenior Technical architecture (Data)Author Commented:
good. tks.
0
marrowyungSenior Technical architecture (Data)Author Commented:
I don't expect I have to try this but if this sunday this happen,  I will try it.
0
marrowyungSenior Technical architecture (Data)Author Commented:
One thing, as the DB is quite large, the restore using script in the question can take a long time, any method to show how many % it done if this script are execute by a SQL job ?
0
David SankovskySenior SysAdminCommented:
See if this works:
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command LIKE 'RESTORE DATABASE' 

Open in new window


or you can try this:

SELECT session_id as SPID, command, aa.text AS Query, start_time, percent_complete,

dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time

FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) aa

WHERE r.command LIKE ‘RESTORE DATABASE’ 

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
so this mean to me that, as right now I am not doing that, so it will only gives result WHEN the restore job is running?
0
marrowyungSenior Technical architecture (Data)Author Commented:
victor, do you know if the Ola backup solution can backup DB on other DB server? right now our maintenance plan do this for us and we'd like to setup ola backup solution on each DB.

and I am not sure if Ola can do this for us too.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No. Ola's solution is based on local instance.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok , tks. I am wondering why the 3 x previous DBA ago do the maintainence backup of Server B from Server A.

server B is the DR of server A by using log shipping ! at this moment, I just backup the SYSTEM DB on the DR server B
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am wondering why the 3 x previous DBA ago do the maintainence backup of Server B from Server A.
I can only see one reason: Take out some load pressure from Production servers.
0
marrowyungSenior Technical architecture (Data)Author Commented:
but that DR server still have the DB backing up, so is it making any diff? on that DR box, I will only do SYSTEM DB backup.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
but that DR server still have the DB backing up, so is it making any diff?
A differential backup isn't a demand so he could prefer to have only a full and many transaction log backups but only the previous guy can tell you what was on his mind when he designed that solution.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"A differential backup isn't a demand so he could prefer to have only a full and many transaction log backups "

only full backup sir. no diff and tlog backup.

"only the previous guy can tell you what was on his mind when he designed that solution."

exactly! this is the first time I see sth like this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.