February 11, 2009 at 4:22 am
Hi guys. I have not done this for a while and cannot recall the steps I took previously. I need to restore a database to a mirror.
From the principal I ran:
ALTER DATABASE DBName SET PARTNER OFF
On the principal the Mirror is broken. On the mirror server the database is restoring
I restored the updated database to the principal. I then backed this up (FULL) then backed up the transaction log.
From the mirror I ran:
--Restore Database to Mirror
RESTORE DATABASE DBName
FROM DISK = 'C:\Mirror\DBName.bak'
WITH MOVE 'Release_Data' TO 'F:\SQLData\DBName.mdf',
MOVE 'Release_log' TO 'G:\SQLLogs\DBName_log.ldf',
REPLACE,NORECOVERY;
GO
THEN
RESTORE LOG DBName
FROM DISK = 'C:\Mirror\DBName.trn'
WITH NORECOVERY;
GO
The database restored OK. The transaction log returned error:
Msg 4312, Level 16, State 1, Line 2
This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
I was then going to run:
FROM MIRROR
Alter database DBName set partner = N'TCP://x.x.x.x:####'
FROM PRINCIPAL
Alter database DBName set partner = N'TCP://x.x.x.x:####'
Any ideas where I have gone wrong?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 11, 2009 at 5:42 am
I seem unable to do anything with the database on the mirror. Can I stop restoring?
Need to get this resolved ASAP......little beads of sweat appearing which is never good!
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 11, 2009 at 6:10 am
The error message occurred due to incomplete sequence of log backups.there may be some log backups that had generated after you took the full backup.Please check the backuphistory system table to find out the latest full and log backups.
one more reason is if you run backup log with truncate only option the sequence of the log file backups breaks down in this scenario you need to run full backup after truncate option.
February 11, 2009 at 6:50 am
Hi. I dropped the mirror database. I then restored the database + log using NORECOVERY.
Now when I try to configure mirroring I receive:
Neither the partner nor the witness server instance for database 'DBName' is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431).
I have had this before but cannot remember how I resolved???
--Set Partner on Mirror
Alter database DBName set partner = N'TCP://x.x.x.x:5034'
--Set Partner on Master
Alter database DBName set partner = N'TCP://x.x.x.x:5034'
..................
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 11, 2009 at 7:05 am
OK all is now happy and well.
I ran ALTER DATABASE DBName SET PARTNER OFF on the mirror.
I then ran SET PARTNER
I will do it all again so all is clear then post my steps for others..........
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 11, 2009 at 7:22 am
Steps taken:
--From Principal
ALTER DATABASE DBName SET PARTNER OFF
--Set Recovery Mode for Principal
ALTER DATABASE DBName SET RECOVERY FULL
--Backup the Principal Database + Transaction Log copy over to Mirror Server C:\Mirror
--Then restore Database to Mirror Server
RESTORE DATABASE DBName
FROM DISK = 'C:\Mirror\DBName.bak'
WITH MOVE 'Release_Data' TO 'F:\SQLData\DBName.mdf',
MOVE 'Release_log' TO 'G:\SQLLogs\DBName_log.ldf',
REPLACE,NORECOVERY;
GO
--Restore Log file to Mirror
RESTORE LOG DBName
FROM DISK = 'C:\Mirror\DBName.trn'
WITH NORECOVERY;
GO
--Set Partner on Mirror
Alter database DBName set partner = N'TCP://x.x.x.1:####'
--Set Partner on Master
Alter database WinMan set partner = N'TCP://x.x.x.2:####'
Hope that helps someone else....
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 17, 2011 at 9:00 am
Check if the LOG backup was taken with NOINIT. The default is to append to the most recent backup set on the media (NOINIT).
when taking Log backup on DISK use the Media Set Options : INIT
June 26, 2015 at 7:03 am
Thank you so much. I was battling for 2 days with the sequencing!
June 26, 2015 at 8:08 am
If you are not sure about t-log you can also take differential bakcup and subsequent t-log backup and restore on the database.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 27, 2015 at 5:56 am
<<The post is 6+ years old but just replying if someone ends up searching this post..! >>
Would like to add 3 more steps
(1) Free up the logspace for principal database involved in the mirroring and make sure it has enough space till you complete the mirroring configuration..
(2) DISABLE any log backup schedules unless and until you restore the full+log backup on mirror otherwise if the database size is big it may take a longer time to setup the mirror and if there are more transaction log backups then you will Un-necessarilly require to load them all on mirror.
(3) Keep an eye on logspace for principal till the mirroring is set as you are disabling it.. 🙂
--From Principal
ALTER DATABASE DBName SET PARTNER OFF
--Set Recovery Mode for Principal
ALTER DATABASE DBName SET RECOVERY FULL
--Backup the Principal Database + Transaction Log copy over to Mirror Server C:\Mirror
--Then restore Database to Mirror Server
RESTORE DATABASE DBName
FROM DISK = 'C:\Mirror\DBName.bak'
WITH MOVE 'Release_Data' TO 'F:\SQLData\DBName.mdf',
MOVE 'Release_log' TO 'G:\SQLLogs\DBName_log.ldf',
REPLACE,NORECOVERY;
GO
--Restore Log file to Mirror
RESTORE LOG DBName
FROM DISK = 'C:\Mirror\DBName.trn'
WITH NORECOVERY;
GO
--Set Partner on Mirror
Alter database DBName set partner = N'TCP://x.x.x.1:####'
--Set Partner on Master
Alter database WinMan set partner = N'TCP://x.x.x.2:####'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply