Question :
I use following PS script to restore database:
$sqlScript = @"
USE master
GO
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE mydatabase
FROM DISK = '\shared_drive_pathbackupmydatabase.bak'
WITH REPLACE
GO
ALTER DATABASE mydatabase SET RECOVERY SIMPLE
GO
ALTER DATABASE mydatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
"@
Invoke-Sqlcmd -ServerInstance "DB_SERVER" -Database "master" -QueryTimeout 720 -Query $sqlScript
When I run it (at 08.05.2019 14:17), database is moved to “Restoring…” state and operation is not finished.
In the meantime, when I run sp_who2 in master database, it returns following output – as you can see transaction 51 is blocked by 55.
+------+--------------------------------+----------------+----------------+-------+--------+------------------+---------+--------+------------------+------------------------------------------------+------+------------+
| SPID | Status | Login | HostName | BlkBy | DBName | Command | CPUTime | DiskIO | LastBatch | ProgramName | SPID | REQUESTID |
+------+--------------------------------+----------------+----------------+-------+--------+------------------+---------+--------+------------------+------------------------------------------------+------+------------+
| 51 | SUSPENDED | mydomainadmin | BUILD_SERVER | 55 | master | RESTORE DATABASE | 0 | 0 | 05.08.2019 14:29 | .Net SqlClient Data Provider | 51 | 0 |
| 55 | sleeping | mydomainadmin | BUILD_SERVER | . | master | AWAITING COMMAND | 1609 | 7 | 05.08.2019 14:29 | .Net SqlClient Data Provider | 55 | 0 |
+------+--------------------------------+----------------+----------------+-------+--------+------------------+---------+--------+------------------+------------------------------------------------+------+------------+
When I preview content of running statements
select session_id, most_recent_session_id, connect_time, last_read, last_write, text
FROM sys.dm_exec_connections AS Conns
CROSS APPLY sys.dm_exec_sql_text(Conns.most_recent_sql_handle)
I am getting following results:
+------------+------------------------+------------------+------------------+------------------+----------------------------------------------------------------------------------------------+
| session_id | most_recent_session_id | connect_time | last_read | last_write | text |
+------------+------------------------+------------------+------------------+------------------+----------------------------------------------------------------------------------------------+
| 51 | 51 | 08.05.2019 14:29 | 08.05.2019 14:29 | 08.05.2019 14:29 | RESTORE DATABASE mydatabase FROM DISK = '\shared_drive_pathbackupmydatabase.bak' WITH REPLACE, RECOVERY |
| 55 | 55 | 08.05.2019 14:17 | 08.05.2019 14:29 | 08.05.2019 14:29 | RESTORE DATABASE mydatabase FROM DISK = '\shared_drive_pathbackupmydatabase.bak' WITH REPLACE, RECOVERY |
+------------+------------------------+------------------+------------------+------------------+----------------------------------------------------------------------------------------------+
and it looks like both transactions are running restore operation.
For me it looks like somehow restore operation is executed once again in another transaction (pid 51) after some time.
Do you know what actually happend? Do you know how to avoid this issue?
Answer :
Cant post it as comment so posting it as answer. This is relevant to your script. Your script should simply look like.
$sqlScript = @"
USE master
GO
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE mydatabase
FROM DISK = '\shared_drive_pathbackupmydatabase.bak'
WITH REPLACE
GO
ALTER DATABASE mydatabase SET RECOVERY SIMPLE
GO
"@
Invoke-Sqlcmd -ServerInstance "DB_SERVER" -Database "master" -QueryTimeout 720 -Query $sqlScript
You do not need to take database offline to restore, again you do not need to bring it online after restore. Restore would do that for you
Can you check what is percent completed for restore operation. If it is hung kill it and start again using the above script. If the percent_complete column is increasing leave it and let it complete.