March 3, 2015 at 1:35 pm
Perry Whittle (3/3/2015)
any luck?
Not yet
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 4, 2015 at 6:43 am
Did you refresh your view in SSMS?
March 5, 2015 at 9:32 am
I don't know what I am missing, but Log shipping is working between instances in the same server, but it is not working between 2 different servers.
Little explanation of what I did
I configured log shipping for one of the DB and followed all the guidelines and got it to work. The Step I took before configuring the log shipping was that I took the backup of the DB on the primary server and restored it on the secondary server. Then I created a “Test table” in the DB on the Primary server just to test if the log shipping would work or not. When all 3 jobs ran successfully (Log backup job, copy job and restore job), I was not able to see the Test table in the DB on the secondary server. When I looked at the job history, all the steps were taken and the history had no errors, but still missing data on the secondary server (didn’t see Test table on the secondary database).
So, this is what I did after that. I configured log shipping on the same server but between 2 different instances and got it to work. However, this time when I created a “Test table” in the DB, the log shipping worked and I was able to see changes on the secondary database.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 5, 2015 at 10:26 am
on the 2 servers that are not working as expected please run the following query.
select differential_base_lsn, differential_base_time
from sys.master_files
where database_id = DB_ID('yourdb')
and type = 0
group by differential_base_lsn, differential_base_time
If the result is the same on the primary and the secondary,
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2015 at 10:33 am
also, dump the step output history and check for logs being skipped, etc. use this query
selectj.name
, s.step_name
, h.message
, h.sql_severity
from
msdb..sysjobs j inner join msdb..sysjobsteps s
on j.job_id = s.job_id
inner join msdb..sysjobhistory h
on s.job_id = h.job_id and s.step_id = h.step_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2015 at 11:36 am
Perry Whittle (3/5/2015)
on the 2 servers that are not working as expected please run the following query.
select differential_base_lsn, differential_base_timefrom sys.master_files
where database_id = DB_ID('yourdb')
and type = 0
group by differential_base_lsn, differential_base_time
If the result is the same on the primary and the secondary,
- take a differential backup on the primary
- disable the LS agent backup job on the primary
- disable the copy and restore jobs on the secondary
- restore the differential to the secondary
- Once done re enable any jobs
- create a new table
- run all jobs and check object exists
Thank you so much for being patience but this is what I did.
Took the differential backup on the Primary
Disable backup job on Primary
Disable copy job and restore job on secondary
On secondary server, when I try to restore by expanding DB, task and restore, I only see 1 option (transactional log) highlighted. Database and files and filegroups option are not highlighted.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 6, 2015 at 4:25 am
T-SQL??
something along the lines of
restore database [thesecondarydb] from disk =
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\primarydb_diff_backup.bak'
with move 'logicalname' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\secondarydb_datafile.mdf',
move 'logicalname' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\secondarydb_logfile.ldf'
, replace, norecovery
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 6, 2015 at 10:21 am
Disabled the log shipping job and recreated it and now it's working. I have no idea why it wasn't working. Didn't take any extra steps. Recreated the same way, I created it multiple times, but it's working.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply