A Script A Day - Day 26 - RESTART a Database Restore

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.

/*

      -----------------------------------------------------------------

      RESTART a Database Restore

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE SQLServer365;

GO

-- Get a list of database files and locations for the restore

sp_helpfile;

GO

-- Change database context

USE master;

GO

-- Backup database

BACKUP DATABASE SQLServer365 TO DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

GO

--Restore Database

RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',

MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

-- STOP SQL SERVER SERVICE WHILE RESTORING

-- Error Message Received on client

-- START SQL SERVER SERVICE

-- There will be a message in SQL log like the below

-- The database 'SQLServer365' is marked RESTORING and is in a state that does not allow recovery to be run.

-- At this point he database is marked as RESTORING and is inaccesible.

-- Change database context

USE master;

GO

-- Restore the database with the RESTART option (this resumes the restore, honestly!)

RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

WITH REPLACE, RESTART, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',

MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

-- Results

/*

Processed 18192 pages for database 'SQLServer365', file 'SQLServer365_Data' on file 1.

Processed 1 pages for database 'SQLServer365', file 'SQLServer365_Log' on file 1.

RESTORE DATABASE successfully processed 18193 pages in 19.852 seconds (7.159 MB/sec).

*/

Enjoy!

Chris