Sql-server – Check progress of database restore running asynchronously

powershellsql serversql-server-2008-r2

I have a Powershell script that restores a database using the SqlRestoreAsync method.

I have a decent size database which I'm restoring (~55Gb) and I'm trying to find the progress of the restore process.

I know that if I run the restore through a T-SQL command then I can view the progress of the restore by querying a few DMV's or with the following query:

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)
            )) AS [SQL]
FROM sys.dm_exec_requests r
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');

This query works fine for T-SQL triggered restores (or SSMS ones), but doesn't show up the restore progress for the restore triggered by the Powershell script.

I can see my database in SSMS's Object Explorer as being in "Restoring" state, so I know it's working, but .. is it running?

The restore process running this in Async mode seems to take a bit more than when running it through T-SQL, so far twice as much and still not finished.

Is there something I'm missing? Any help would be appreciated.

Best Answer

I don't see anything obvious about why this command won't be showing up like any other restore command, unless it is busy doing something other than RESTORE DATABASE.

I suggest identifying the session_id PowerShell is using (you should be able to see in sys.dm_exec_sessions or sys.dm_exec_connections), or you could change the script to obtain it and print it to the console. Then change your filter to:

SELECT command, status, percent_complete, 
  wait_type, last_wait_type, wait_resource,
  blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = <x>;

This will help you verify:

if the command is actually a RESTORE or if Powershell is actually doing something else first
current status
percent complete, if that is available (it isn't always)
what it's waiting on (and if you run it repeatedly, if this is changing)
if your restore is actually being blocked by anything

Related Solutions

Sql-server – Restore all applicable files in a backup device in T-SQL

This procedure should do what you want:


create procedure dbo.dbrestore
@dbname nvarchar(50)
as
declare @fileno integer
declare @dumpdevice nvarchar(50)
declare @mediasetid integer
declare @position integer

select top(1) @dumpdevice=a.logical_device_name, @mediasetid=b.media_set_id, @position=b.position from backupmediafamily a inner join backupset b on a.media_set_id=b.media_set_id
where b.database_name='backuptest' and b.type='D' order by a.media_set_id desc, b. position desc

declare restore_cursor cursor for
select position from msdb.dbo.backupset where database_name=@dbname and type='D' and media_set_id=@mediasetid and position=@position
union
select  max(position) as position from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position
union
select position from msdb.dbo.backupset where database_name=@dbname and type='L' and media_set_id=@mediasetid and position>@position
and last_lsn>(select max(last_lsn)from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position)
order by position asc;

open restore_cursor
fetch next from restore_cursor into @fileno
while @@FETCH_STATUS = 0
begin
--comment the print statement and uncomment the exec statement to run
--exec('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+@fileno+';')
  print ('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+convert(varchar(4),@fileno)+';')
fetch next from restore_cursor into @fileno
end
close restore_cursor
deallocate restore_cursor
--exec('restore database ['+@dbname+'] with recovery;')
go

execution goes:

get the name of the dumpdevice based on the supplied database name. I'm assuming that they are all on the same device. The top is to work around having multiple media sets per database if using 'with format' for the initial DB, and to cope with the possibility of multiple full backups in the device

Then build up a union query consisting of:

The last full database backup (type=D) in the device

the last differential backup (type=I and has the greatest LSN (Log Sequence Number)) taken after the last full db backup.

the transaction logs (type=L) taken after the above diff.

returning just the position value, which maps to the FILE value in this case (http://msdn.microsoft.com/en-us/library/ms186299.aspx)

Loop through the result restoring them individually, and then a final restore with recovery to finish it off.

Call it as:


exec dbrestore 'db2restore'

I've tried it on a test db and it appears to work happily, and comparing it to the files SSMS wants to restore for the same operation it looks the same.

Provided as is, there's probably typos, I've probably missed a possible boundary condition, and there's no error checking.

Sql-server – SQL Server 2008 R2 Restore constantly hanging

I think I have found the issue.

The backups are stored our data domain, so when performing the restore we select the backups from this location. I think there is some type of connection issue between this server and the data domain. Because when I move the backup files over to the server and restore from the server directly, I cannot reproduce the issue and the restores are probably 10x faster.I set a continuous restore of a database (11 GB) that has been running for about 25 minutes and has completed the restore successfully 23 times with no hanging

My thinking is that the connection issue probably lies somewhere with our test server still. Because we use the Data Domain for all of our production backups (although it is changing) and have no problems restoring elsewhere, including the databases I have been working with. Not to mention, we have automated nightly restores on other servers that have never failed.

I think this at least pinpoints the issue. I will look into where the connection issue is and work with the server\networking guys to see if we can resolve it.

Thanks you guys for your quick assistance

Best Answer

Related Solutions

Sql-server – Restore all applicable files in a backup device in T-SQL

Sql-server – SQL Server 2008 R2 Restore constantly hanging

Related Question