Restore Database WITH RESTART


Last night, I was migrating a production database  from SQL Server 2005 to SQL Server 2008 environment. While the restoration script was running, failover happened due to a network failure. When the SQL instance started on the other node, all the databases are came online; however, the database, where I was doing a restore, went into restoring mode. So I tried to bring the database online by executing the following command:

USE [master];
GO
RESTORE DATABASE [SKG_TEST] WITH RECOVERY;
GO

Unfortunately, I was unable to bring the database Online, so the next thought of asking for help. I had posted my query on twitter, including the #SQLHelp hashtag. Within a few moment, I got two reply, one was to use the above command, the second one to use “WITH RESTART” clause along with the existing restore script. As I never used this clause earlier, so I spend a little time to read more about it. A little search on MSDN, helped me. I got two articles on how to use the WITH RESTART clause, you can read more about it here and here. As the above articles matches with my scenario, I thought of trying the same. I re-execute the restore script with RESTART clause, and the restoration of the databases started immediately.

-- Restoring Database With RESTART option
USE [master];
GO
RESTORE DATABASE [SKG_TEST] FROM DISK = 'E:\Migration\SKG_TEST.bak'
WITH MOVE 'SKG_TEST' TO 'E:\USERDB\DATA\SKG_TEST.MDF',
MOVE 'SKG_TEST_LOG' TO 'E:\USERDB\LOG\SKG_TEST_LOG.LDF',
RESTART, REPLACE;
GO

After a few hours, the restoration was completed.  After the restoration, the logins were transferred and the migration process was completed. I would like to thank the SQL Community for their quick response, Thanks everyone for your quick reply. It saves me a few hours 🙂

Happy Learning 🙂

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore, How To, Just Learned, SQL Server and tagged , , , . Bookmark the permalink.

6 Responses to Restore Database WITH RESTART

  1. blakhani says:

    Thanks for Sharing!

  2. khautin says:

    It is a useful tip!

  3. vic says:

    I tried this…
    Full restore of TEST DB takes 22 secs.

    so the test is like so…
    1.) restore database and stop at 50% complete
    2.) restore database with restart .. completes in 22 secs
    it’s as if it’s not continuing where it left off…

    Does RESTART mean to resume from the last completed file in a backup file? All of my backups files only contain a single backup.

  4. amuthan says:

    really it’s very good scenario,

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