RESTART the Restore

How to begin the restore process from where it got failed ?

Note: This applies to SQL Server 2005 or later.

In every SQL Server DBA's day to day experience, backup and restore is a common thing and we get used to it as we do lot many restores. I'm sure that at some point of time we would have got into the situation where the restoration process failed in between or just before the restore gets completed and this happens because of many reasons. One of the common issue I found most of the times is with because of the communication drop between the SQL Server and the backup file.

For example, if we do a restore from a file which is located in a network path and because of what ever reasons, SQL Server lost the communication with the backup file while the restore is going on. After waited for some time, SQL Server will just terminate the restore session and we will see a error message pertaining to the error like no communication etc. If the restore is with a small sized database, we could bear to start the restart the restore. But, Imagine if we are dealing with large database restoration, it would be a for sure problem.

I had same situation in my experience that we needed to perform a restore of 600+ GB database every month between two SQL Server instances located at different geographical locations and the restore was taking more than 24hrs of time all the time as the backup file is located remotely and we were using remote share path in the restore command. If the restore fails, we need to restart the restore which has increased the outage for the application. If we have a way using which we can resume the restore from the point where it got failed, we will be very happy to touch the restore of that database again and this will be helpful to minimise the downtime for the applications.

Using the option "RESTART", the resume of the restore process if possible. Using this option is very simple like any other options including MOVE, REPLACE etc.

Let me show a demo of using it. I have the backup file in a USB drive which is connected to my system as D: drive. I initially used the script below to restore the database to my instance.

USE [master]
RESTORE DATABASE [RESTART] FROM DISK = N'D:\SQL Server\ADWDB2012.bak' WITH 
MOVE N'AdventureWorksDW2012_Data' TO N'C:\1\AdventureWorksDW2012_Data.mdf', 
MOVE N'AdventureWorksDW2012_Log' TO N'C:\1\AdventureWorksDW2012_log.ldf', STATS = 5

After the restore is started and shown as 10%, I have un-plugged the USB drive to make the restore process fail and I could see the below error after I did that.

5 percent processed.
10 percent processed.
Msg 3203, Level 16, State 1, Line 2
Read on "D:\SQL Server\ADWDB2012.bak" failed: 1110(The media in the drive may have changed.)
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally. 


So, now we have the restore process failed. Let's try using the option of RESTART and see how it goes. I have plugged-in my USB drive which has the backup file and executed the same query above adding RESTART option as you can see below.


USE [master]
RESTORE DATABASE [RESTART] FROM DISK = N'D:\SQL Server\ADWDB2012.bak' WITH 
MOVE N'AdventureWorksDW2012_Data' TO N'C:\1\AdventureWorksDW2012_Data.mdf', 
MOVE N'AdventureWorksDW2012_Log' TO N'C:\1\AdventureWorksDW2012_log.ldf', 
STATS = 5, RESTART

Now, I could see the database is restored from there and became accessible.

I would like to alert you about the checkpoint file a restore process creates and the impact if we delete that file with using the RESTART option. When you do a restore operation, SQL Server creates a checkpoint file in the default backup location with .CKP extension as you can see it here.

Because of what ever reasons if this file is not available or accessible, the RESTART option will not help and we can see the below message.
 The restart-checkpoint file
'C:\Program Files\Microsoft SQL Server\MSSQL11.V2014\MSSQL\Backup\RestoreCheckpointDB6.CKP' was not found.
The RESTORE command will continue from the beginning as if RESTART had not been specified.

Hope this will help you when you have a restore process fails and need to restart.