How to Restart an Interrupted SQL Server Database Restore
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.
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.
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.
- Read Attach a SQL Server database with a missing transaction log file
- Read Limitations when attaching a SQL Server Database
- Read Point in Time Recovery Using New Timeline Feature in SQL Server 2012
- Read more about other RESTORE options
Last Updated: 2012-05-21
About the author
View all my tips