Restart an Interrupted Database Restore in SQL Server
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.
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
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
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
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
Geeks who read this article also read…
- Performance Dashboard Reports in SQL Server 2012
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…