Restart an Interrupted Database Restore in SQL Server


Aug 31, 2013

Introduction

There can be an instance when you could be in the middle of restore a very large database on a clustered instance and due to certain network issue the failover had happened. You would discover that once the SQL Server Service came online on the second node the database which you were restoring shows (Restoring…). This scenario can be witnessed on a non-clustered instance as well especially when someone had accidentally restart SQL Server Service or the server is rebooted.

During such scenarios RESTORE DATABASE …. WITH RESTART command which is there in SQL Server 2000 and Higher Versions becomes very handy. This command can be used by the database administrator to restore database which was interrupted while in the restore operation. In the below snippet you could see that AdventureWorks2012 database is in Restoring… state. In case your database is in Suspect Mode then the following article How to repair a Suspect Database in SQL Server will be of help.

Restart an Interrupted Database Restore in SQL Server

Important Note: There can be an instance that you have run the Differential or Transaction Log restore command WITH NORECOVERY clause. In such scenarios you must run RESTORE DATABASE <NAME> WITH RECOVERY

Newsletter Signup

In case of an interrupted restore database restore operation DBA can execute RESTORE DATABASE …. WITH RESTART command to compete interrupted database restores operation.

Execute the below TSQL code to complete the database restore operation

RESTORE DATABASE [AdventureWorks2012]
FROM DISK ='I:\Backups\AdventureWorks2012.bak'
WITH RESTART, STATS = 25
GO

Once above command is executed successfully your database will be ready to accept user connections. If you would like to know how much time is required to complete the database restore then read the following article

Practice

Imitate this scenario on a development server by restarting SQL Server Services or by rebooting the server while a medium sized database is in restoration state. Read the following article to know How to Remotely Shutdown, Restart or Log Off Windows Server across the network


Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."