Restore DB with Norecovery

Question

Restore DB with Norecovery

tsd_suresh

SSC Enthusiast

Points: 111
More actions
November 12, 2009 at 1:11 am

#213952

Hi ,
I am trying to restore DB with no recovery model for Log shipping. while i try to restore from backup with No recovery model . But it hangs all over there , just showing message "restoring " .Please help me .
Regards
Suresh

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

I assume you are seeing the "restoring" message on the restore dialog withing SSMS.

If so, how big is the database? Don't forget the first part of the restore will be creating the mdf file at its required size, and initialising every page. For a large database, it can take ages before you see the progress bar first start to move.

To confirm that something is happening, have a look at the activity on the server during the restore (you can use good old fashioned sp_who2 if you want), and look for the spid that is doing the restore (it should show "RESTORE DATABASE" or something like that in the Command column)... as long as the DiskIO column is increasing, then it's doing something and you'll just have to wait.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

tsd_suresh (11/12/2009)
I am trying to restore DB with no recovery model for Log shipping. while i try to restore from backup with No recovery model . But it hangs all over there , just showing message "restoring "

Yup, that's exactly right. You specified NORECOVERY so the DB is not brought online but is left in the restoring state so that further log backups can be restored.

Did the restore complete (dialog box if you're using the gui or execution completion using T-SQL script)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tsd_suresh SSC Enthusiast Points: 111 More actions · Answer 3

Hi ,

I am using a small size DB ( 2 GB Only ). I tryed to uses SSMS and T-SQL to restore the Database with NORECOVERY but both get stucking . I used SP_who and activity monitor to view the activity, no message seen like restroing.

Any other alternative method to restore with norecovery ? roughly how long it will take to restore ? i have waited one hour to restore , but not completed the process

Thanks

Suresh

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

Can you explain exactly what you are doing, and exactly what you are seeing (and where)... e.g. if you are running the restore using TSQL, can you post the TSQL, and the output.

As the database is only 2gb, and you say you have waited for over an hour, then unless you have some SERIOUS I/O issues, what I suggested will not be the case.

It's more likely to be what Gail is suggesting, and the database has been successfully restored, but you left it in NORECOVERY waiting for more transaction log and/or differential backup to be restored. If so, and you are certain you don't have further logs to restore, you can bring the database online by doing:

RESTORE DATABASE <dbname> WITH RECOVERY

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

It will be in Restoring state only unless and until you bring them into Online

since you are using for Logg shipping it will be in Restore state only.

Once any problem occurs in Py Server you can bring Sec server by user

RESTORE DATABASE <YOUR DATABASE> WITH RECOVERY see to that there are no users accessing

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

tsd_suresh (11/12/2009)
Any other alternative method to restore with norecovery ?

Restore with recovery?

Depends what you want to do with the DB. If you're restoring additional backups, then you need to restore with norecovery. If you're not, then restore with recovery.

Please take a look at the section "Restore Database" in SQL's Books online

roughly how long it will take to restore ? i have waited one hour to restore , but not completed the process

Takes about 5 minutes for a 1 GB database to restore on my laptop. My guess is that you did the restore WITH NORECOVERY and are waiting something that will never happen. If you restored WITH NORECOVERY, the DB will not come online until you issue the command to bring it online or restore another backup (log or differential) using the WITH RECOVERY option.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55539 More actions · Answer 7

Is this the target database that is receiving logs from log shipping ?? If so, I think you want it to stay in NORECOVERY so it can accept more transaction logs. (I don't remember log shipping very well)

tsd_suresh SSC Enthusiast Points: 111 More actions · Answer 8

Hi All,

Thanks for your reply. I understood it will be restore mode only. My main intention is to configure log shipping. so i took backup from Primary server and restore in secondary server. when i restore with norecovey model in secondary server shows "restoring".it is cleared now. thanks a lot

Thanks

Suresh