Home > SQL server DBA administration > Save your Backup and Restoration time by Restart option:

Save your Backup and Restoration time by Restart option:


In our project support we have to restore the 300 GB backup and it will take around 7 hours. The backup file was in central file server location . It was a network restoration  which is failed due to network glitch sometime and we have to start the process again. In sql server 2008 onwards sql introduce RESTART option which helps to resume the backup and restore operation on the same point where it was interrupted .The RESTART option saves time because it restarts the backup and restore  operation at the point it was interrupted. To RESUME  a specific backup and restore operation, repeat the entire BACKUP  and RESTORE statement and add the RESTART option. If it is failed after 3 hours and again we start the process with RESTART option then it will completed within 5 hours and we can save 2 hours time.

Example:

-- Restore a full database backup of the Test2 database.
 

RESTORE DATABASE [test2] FROM  DISK = N’D:\SQL Database Backup\test2_backup_2012_10_26_075210_1274440.bak’ WITH RESTART, FILE = 1,  MOVE N’test2_log’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf’,  NOUNLOAD,  STATS = 10

GO

 

GO
-- The restore operation halted prematurely.
-- Repeat the original RESTORE statement specifying WITH RESTART.
 

RESTORE DATABASE [test2] FROM  DISK = N’D:\SQL Database Backup\test2_backup_2012_10_26_075210_1274440.bak’ WITH RESTART, FILE = 1,  MOVE N’test2_log’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf’,  NOUNLOAD,  STATS = 10

GO

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: