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.
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.
Next Steps
