asked on

SQL Server Restore: Closed Restore Window before Restore was Complete. Did I interrupt the restore?

SQL Server restore of a large BAK (1.5 TB). I started it just fine in SSMS but accidentally closed the restore windows so I can't see the progress.

In running this query, I don't see anything running except my SELECT.

SELECT session_id AS SPID, command, a.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) a

The database has the "(Restoring)" next to the database name in SSMS. If I try to go into the database, it says it's not available.

Did I interrupt the restore by closing that window or could it still be running?

Kelvin Sparks

Check the log under the management tab and see if the restore completed or failed - If just started in log and not anything to say completed or failed, then I guess it is still running.

Kelvin

Julie Kurpa

ASKER

Thanks Kelvin. I looked at the SQL Server Log. I don't see where the DB restore started. I am doing this from SSMS on another server rather than the server I started the restore on. Does that matter?

Kelvin Sparks

Yes, under the management node there, you should see SQL Server Logs - open the current one in there.

Julie Kurpa

ASKER

The only thing I see in the log is from when I logged on and it says the following about my database:

Login failed for user "sa'. Reason: Failed to open the explicitly specified databaset 'MYDB'. [CLIENT: <local machine]

Julie Kurpa

ASKER

I'm about to go home for the evening. I thought I'd just let it sit until tomorrow.

Is it required to keep that restore window open while a restore is running? You know, where you can see the progress at the bottom?

ASKER CERTIFIED SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andrei Fomitchev

To verify the status of a DB including the result of restore I usually use:
USE [YourDB]
DBCC CHECKDB;
GO

I had the case when LOG showed DB Restore is OK, but DB was corrupted before backup - we couldn't use it. I had to restore from more recent backup.

Julie Kurpa

ASKER

The output from the DBCC CHECKDB says:

Msg 927, Level 14, State 2, Line 1
Database 'MYDB' cannot be opened. It is in the middle of a restore.

Julie Kurpa

ASKER

Since it says it's in the middle of a restore, my colleague and I want to give it another day/half since it's such a huge DB. If it doesn't finish on Thursday, then we'll restart it.

Thanks for the guidance Kelvin on dettach/reattach. Will update in a couple days unless someone has something I should do now.

Much appreciation.

Andrei Fomitchev

You can monitor progress of restore/backup:

SELECT session_id as SPID, command, a.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) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

DBCC CHECKDB works when restore has been completed.

Julie Kurpa

ASKER

Thank you Andrei. I already posted that I did that command (only I didn't specify the WHERE) and got no output.

Kelvin Sparks

I've located this code that will give you some history as to when restoration of databases started and finished. Try running that against your server

SELECT    [rs].[destination_database_name],    [rs].[restore_date],    [bs].[backup_start_date],    [bs].[backup_finish_date],    [bs].[database_name] as [source_database_name],    [bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ORDER BY [rs].[restore_date] DESC

Open in new window

Julie Kurpa

ASKER

That's a very good query! Definitely one for my scrapbook!

The output I get shows all the other restores we did but the one in question is not listed.

This is looking more and more like the restore is not running.

Do restores show at an OS level?

Kelvin Sparks

You could look at CPU activity, but I don't know what other databases are running on that server that could affect that. As I mentioned in an earlier post, I don't think you can close that restoring window without the restore completing or cancelling, so I tend to agree. So I guess the best you can do is to dettach, reattach and restore again.

Kelvin

Julie Kurpa

ASKER

We decided to delete the database and restart the restore.

Now when I do the query, I see the RESTORE DATABASE running

Thanks everyone!