marrowyung
asked on
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
MOVE N'VMSVA_02_Indexes' TO N'D:\Database\MDF\xxxVA_02
MOVE N'VMSVA_02_Audit' TO N'D:\Database\MDF\xxxVA_02
MOVE N'Partition_1' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_2' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_3' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_4' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_5' TO N'D:\Database\MDF\Partitio
MOVE N'VMSVA_02_log' TO N'D:\Database\LDF\xxxVA_02
NOUNLOAD, REPLACE,
STATS = 10
GO
any way to disable/kill the existing DB connection and let the restore works well ?
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
MOVE N'VMSVA_02_Indexes' TO N'D:\Database\MDF\xxxVA_02
MOVE N'VMSVA_02_Audit' TO N'D:\Database\MDF\xxxVA_02
MOVE N'Partition_1' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_2' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_3' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_4' TO N'D:\Database\MDF\Partitio
MOVE N'Partition_5' TO N'D:\Database\MDF\Partitio
MOVE N'VMSVA_02_log' TO N'D:\Database\LDF\xxxVA_02
NOUNLOAD, REPLACE,
STATS = 10
GO
any way to disable/kill the existing DB connection and let the restore works well ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
but MS SQL server should still allow to overwrite,100% no ?
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.
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.
ASKER
"Trying to force a restore with connected users can cause even more damage than there is right now. "
e.g, data corrupt ?
e.g, data corrupt ?
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.
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.
ASKER
yeah, ok , just try to figure more in detail. tks.
ASKER
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.
will come back even after this ticket close if I really need to but I don't want.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so the solution above is good to close all connection, anything you can suggest ?
ASKER
Dear all,
please help on this if you can:
https://www.experts-exchange.com/questions/28728699/table-structure.html
please help on this if you can:
https://www.experts-exchange.com/questions/28728699/table-structure.html
Yes, close all connections is the only possibly suggestion.
ASKER
good. tks.
ASKER
I don't expect I have to try this but if this sunday this happen, I will try it.
ASKER
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 ?
See if this works:
or you can try this:
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' 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’ ASKER
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?
ASKER
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.
and I am not sure if Ola can do this for us too.
No. Ola's solution is based on local instance.
ASKER
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
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
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.
ASKER
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.
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.
ASKER
"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.
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.
ASKER