December 27, 2017 at 1:01 pm
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.
December 27, 2017 at 1:26 pm
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).
December 27, 2017 at 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.
Things will work out. Get back up, change some parameters and recode.
December 27, 2017 at 3:16 pm
WebTechie - Wednesday, December 27, 2017 1:43 PMThat 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
January 2, 2018 at 12:57 pm
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.
January 24, 2018 at 3:06 am
This was removed by the editor as SPAM
January 24, 2018 at 8:37 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply