Restart an Interrupted Restore Operation

What exactly we normally do when a restore is interrupted, we restore the database again. What if you can restart the restore where it was interrupted? Quite Good !Right

Microsoft SQL Server Added the option Restart in the Restore command from SQL Server 2012.The  WITH RESTART option checks the checkpoint of the interrupted backup and start the restore from that point.

RESTORE DATABASE [AdventureWorks2014] FROM  DISK = N'H:\Database Backup\AD_FULL.BAK' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5,
RESTART

In case you want to see what happening behind the restore enable the below trace flags and start the restore

dbcc traceon(3004, 3605, -1)

Messages recorded in error log file

DBCC TRACEON 3004, server process ID (SPID) 57. This is an informational message only; no user action is required.
DBCC TRACEON 3605, server process ID (SPID) 57. This is an informational message only; no user action is required.
Restore(AdventureWorks2014): Acquiring U lock on the database
Restore: Attempting to use WITH RESTART
Restore: RESTART Checkpoint loaded
Restore(AdventureWorks2014): RESTORE DATABASE started
Starting up database 'AdventureWorks2014'.
The database 'AdventureWorks2014' is marked RESTORING and is in a state that does not allow recovery to be run.
Restore(AdventureWorks2014): Acquiring X lock on the database
Restore(AdventureWorks2014): Acquired X lock on the database
Restore(AdventureWorks2014): Reopening the backup set
Restore(AdventureWorks2014): Beginning OFFLINE restore
Restore(AdventureWorks2014): Preparing containers
Restore(AdventureWorks2014): Containers are ready
Restore(AdventureWorks2014): Restoring the backup set

Leave a Reply

Your email address will not be published. Required fields are marked *