How to Restart an Interrupted SQL Server Database Restore

By:   |   Comments (8)   |   Related: > Restore


Problem

One of the junior SQL Server Database Administrators in my company approached me yesterday with a dilemma. He was restoring a large database on a Failover Cluster Production Server and while the restore was in progress, due to network failure, the restore failed. Once the SQL Server came up on the other node all the databases came up, except for the database which he was restoring prior to the failover. In this tip we will take a look at the command RESTORE DATABASE...WITH RESTART to see how this command can be helpful during such scenarios.

Solution

The RESTORE DATABASE...WITH RESTART command is a very useful command which is available in SQL Server 2005 and higher versions. A Database Administrator can use this command to finish restoring an interrupted database restore operation.

In the below snippet you can see that ProductDB is in a (Restoring...) state once the SQL Server came online after the unexpected failure.

Database Restoring... State

During such scenarios one can execute the RESTORE DATABASE...WITH RESTART command to successfully complete the database restore operation.

Below are two commands.  The first gets a list of the backups on the file and the second does the actual restore with the restart option.

-- get backup information from backup file
RESTORE FILELISTONLY
FROM DISK ='C:\DBBackups\ProductDB.bak'
GO
-- restore the database
RESTORE DATABASE ProductDB
FROM DISK ='C:\DBBackups\ProductDB.bak'
WITH RESTART
GO

Below you can see that after running the RESTORE DATABASE...WITH RESTART command the database was successfully restored allowing user connectivity.

Successfully Restore Database
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, November 2, 2016 - 6:49:11 AM - Cyril Back To Top (43678)

 

Hi,

I so happen to be reading BACKUP on https://msdn.microsoft.com/en-us/library/ms186865.aspx

It states the below

Compatibility Options

RESTART
Beginning with SQL Server 2008, has no effect. This option is accepted by the version for compatibility with previous versions of SQL Server.

Does this command have any effect or just does something similar to REPLACE?

Tuesday, October 18, 2016 - 6:42:25 AM - Ravinder Back To Top (43586)

Restarting Interrupted Restore Operations in SQL Server. If a restore operation is interrupted, you can restart the operation from the point of interruption. This can be useful if you restore large databases.

Wednesday, February 27, 2013 - 9:38:33 AM - Jefferson Silva Back To Top (22443)

I'm Brazilian.

Thank you! You save my work!! rsrs.

Continue help us.

 

 

Friday, June 8, 2012 - 9:56:36 AM - Aaron Sentell Back To Top (17884)

Thanks for the tip. When you specify WITH RESTART does that mean the restore process picks up where it left off or, as the command suggests, does it restart the restore operation from the beginning? If from the beginning then what's the advantage of WITH RESTART over WITH REPLACE?

 

Wednesday, May 23, 2012 - 5:35:41 PM - Greg Robidoux Back To Top (17620)

@bugbait - thanks for pointing that out.  The typo has been fixed.

Wednesday, May 23, 2012 - 5:26:00 PM - bugbait Back To Top (17619)

Typo? "due to network failure, the backup failed." = "due to network failure, the restore failed."

Handy Info, thanks.

Wednesday, May 23, 2012 - 1:42:08 PM - Levi Back To Top (17613)

@Kalyan - It would fail complaining that the database already exists, unless you specified the WITH REPLACE option, in which case it would start the restore all over again from the beginning.

Tuesday, May 22, 2012 - 9:42:29 AM - Kalyan Back To Top (17589)

What will happen if we use normal Restore command without RESTART Option? Please Clarify














get free sql tips
agree to terms