October 8, 2012 at 2:14 am
Hi every body
I have a database in server A and i have a friday job that takes a full and a daily job that occurs every 5 minutes and takes log. my boss wants me to restore database on server B and keep the databae on server B ,sync with server A (for just reporting),the problem is that both of the sql servers are in windows authentication mode and they cant see each other so i cant have log shipping.
what i did is :
i use map network to see the drive on server A to have the backup files and restore full backup with standby (because we just want to select from it).
how can i restore all log files and keep it sync with server A?
i create a job and with use of EXEC xp_cmdshell 'dir d:\' ,insert the log backups in a table
then with a cursor i restore them one by one (i select only logs that were taken today)
and this job occures onec at night, but he wants a better way,he said find a way like registering backups(full and log) in server B.is it possible?or does any body have better idea?
October 8, 2012 at 10:04 pm
No sense in reinventing the wheel. Even though the servers cannot communicate directly you can still use Log Shipping. You'll just need to automate the copying of the tran log backups from server A to a place where server B can reach them so it can restore them.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 8, 2012 at 11:34 pm
thanks a lot for your reply
how can i restore logs to server B?
i use Map Network Drive so i have a drive for backups in server B but in log shipping , secondary server instances and databases when i want to add server B to set restore transaction log how can i connect to server B?
October 8, 2012 at 11:58 pm
mah_j (10/8/2012)
i use map network to see the drive on server A to have the backup files
The way i am understanding what you have said above is that on server b you have a mapped drive to server a, is that correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 9, 2012 at 12:12 am
yes exactly as you said.
October 9, 2012 at 1:19 am
mah_j (10/9/2012)
yes exactly as you said.
So how can you state that the servers "cant see" each other.
The access you are describing requires Netbios ports to be opened!!!
If you can map a drive between servers you can use Log Shipping 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 9, 2012 at 1:35 am
both of sql servers are in windows authentication mode i cant connect to server B from server A
also the log on services for both of them are local system.
October 9, 2012 at 1:41 am
are the servers in different domains or a workgroup?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 9, 2012 at 1:50 am
the servers are in a workgroup.
October 9, 2012 at 2:36 am
October 10, 2012 at 1:04 am
many thanks
that was a helpful article:-)
October 10, 2012 at 1:10 am
Thanks, if you found the article helpful please don't forget to rate it 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2012 at 6:39 am
transactional replication is also an option if the Server B database is only for reporting purposes
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply