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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10275 Visits: 1265
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
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4693 Visits: 3122
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10275 Visits: 1265
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 Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72358 Visits: 14916
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10275 Visits: 1265
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.
Lincoln Burrows
Lincoln Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16717 Visits: 2105
Hi,
There are multiple reasons for the error msg 3013, level 16, state 1, line 1 backup database is terminating abnormally. Some of them are mentioned here:-
1. Write failure occurred while creating the backup: When there is insufficient storage space on the backup drive, the file gets compressed.
2. When unauthorized users attempt to view network drive, SQL Server may result in the error.
3. Media failure occurred: When the storage device where the backup file is saved undergoes any media failure, this may also result in SQL Server Error
4. When the database is in Suspect mode, and the user tries to perform a backup of the transactional log, the error may occur.

Solution:
1. Take the database offline after changing the logical name of the database and then again bring the database online.
2. change the logical name of the database, insert a white space at the end of the new file name
For complete steps read the article: SQL Server Error 3013 - Quick Fix

Thanks
goher2000
goher2000
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4083 Visits: 1664
I usually use the script to generate restore commands and run "restore database <dbname>" command in the end to recover the database, it makes it much easier.


-- choose database or currently used database will be selected
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()

-- Get Backup History for required database

SELECT
s.server_name,
s.database_name,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.user_name,
s.backup_start_date,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
s.recovery_model, CASE s.[type]
WHEN 'D' THEN 'Restore database ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + REPLACE(m.physical_device_name,'Y:','X:') + ''' with norecovery, replace'
WHEN 'I' THEN 'Restore database ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + REPLACE(m.physical_device_name,'Y:','X:') + ''' with norecovery'
WHEN 'L' THEN 'Restore log ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + REPLACE(m.physical_device_name,'Y:','X:') + ''' with norecovery'
end as cmd
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
and backup_start_date >= (select max(x.backup_start_date) from msdb.dbo.backupset x where x.database_name=@db_name and x.type='D')
ORDER BY backup_start_date ,
backup_finish_date
go



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