SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to put large database into norecovery state


Need to put large database into norecovery state

Author
Message
Glenn Tucker
Glenn Tucker
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6510 Visits: 1166
Hello,

I have two servers in an Availability group. Servers A is primary and Server B is secondary.

For some reason, the AG was broken and the secondary database "MainDB" was not synchronizing.

1. I removed the database from the availability group on Server B.
2. Dropped the database.
3.Added it to the AG through the wizard. It timed out and failed.
4. I restored the full backup with no recovery. I then restored all of the transactions logs with norecovery through the gui.

However, that didn't leave the database recovering as I thought it would.

Now when I try to restore additional log files with norecovery through script, I am getting an error.

Msg 3117, Level 16, State 1, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.


The database is 685 GB and takes a long time to restore. I am not sure why restoring the log files with norecovery didn't leave the database is a recovering. I know at this point, I really don't want to drop the database and start all over. It will take hours.

I tried executing a "restore database mainDB with norecovery. That didn't work.

Msg 3153, Level 16, State 1, Line 1

The database is already fully recovered.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.



Can anyone shed some light on how to get this added back into a norecovery state so I can add it back into the AG?

Things will work out. Get back up, change some parameters and recode.
Sreekanth B
Sreekanth B
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2465 Visits: 2359
Hi Glenn,
If it says the database has been fully recovered/restored, you pretty much have no options to revert at this point. You have to basically restart the restore process with your FULL backup--> then your T-logs (Make sure WITH NORECOVERY is used).
Glenn Tucker
Glenn Tucker
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6510 Visits: 1166
That was my thought also, but I wanted to put the question out just in case.

I had norecovery for full backup restore as well as the log files during my last restore attempt. This time, I will use query analyzer instead of the gui to make sure it is "norecovery".

It just that restore took 3 hours.

Thanks.

Things will work out. Get back up, change some parameters and recode.
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33550 Visits: 9539
WebTechie - Wednesday, December 27, 2017 1:43 PM
That was my thought also, but I wanted to put the question out just in case.

I had norecovery for full backup restore as well as the log files during my last restore attempt. This time, I will use query analyzer instead of the gui to make sure it is "norecovery".

It just that restore took 3 hours.

Thanks.


Unfortunately you will have to start over.
In my opinion, learn to do backups and restores using t-sql instead of the GUI. There seems to be too many issues, display problems and such using the GUI which is further complicated by the display issues being different on different versions of SSMS. There is no question on what's being executed when using t-sql. The GUI can become a bit of a black box.

Sue



Glenn Tucker
Glenn Tucker
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6510 Visits: 1166
Sue,

You have the right of it.

I am very adept at scripting in SQL Server. However, I normally only use scripting for backups when I want to stripe a backup or moving the tempDB database or something else particular.

Now that I am using and deploying more AGs, I am finding that scripting works better in that regard as well. I set up the restore logs for the AG. Right before I hit ok, I hit the script to a new window.

To my surprise, the norecovery statement had not been added. I had it checked in the GUI. This is why my database was not left available for the AG as a secondary.

I had to restart the process all over again. Thankfully, it didn't take a long as the first time. But now when I need to set up a database to be added as a secondary and the database is large, I am sticking with scripting the backups.

Thanks.

Things will work out. Get back up, change some parameters and recode.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search