Today’s script is another one based on database restores. There is a little known clause of the RESTORE DATABASE command called RESTART. The name of this clause is deceptive, it does not RESTART anything and should actually be called RESUME or CONTINUE. What it allows you to do is to resume a restore that has failed for a reason other than a SQL Server issue (backup consistancy, lack of space etc).

Take the following example which is simulated by the script. You start a large backup lets say 500GB restoring before you leave the office one evening which you know from previous restores takes about 4 hours to run. Now being a DBA you will of course log on ;) to check the restore is succesfull and finish any other tasks required before the database is ready to be used. You notice the database is in a RESTORING state but your query window with the restore command has errored, after a bit of digging you find that 3 hours and 50 minutes into the restore the server lost power and went down (assume this is a development environment with no UPS or fail over) and the IT operations team brought the server back online.

If you where to restore the database again then it would take another 4 hours to run but by using the RESTART clause the database restore will only take about another 10 minutes!

Now I haven’t used this in anger but have tested it quite a few times on SQL Server 2008 and SQL Server 2008 R2 and have never had a problem.