SQL Server Performance Forum – Threads Archive

manual way of doing log shipping

hi guys,
after many attemps to do log shipping on my SQL server 2005. I am looking for a manual way of doing it without having to use any domain account to start sql Services.
like:
1- Backup the Tlog on the primary server
2- copy the logs to the secondary server using windows tasks
3- restore the logs on the secondary server. What do you guys think??? please advice AKTHAR

I have a friend that also does it on his own in a similar way you described. No problem with it. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

Can also take help of Brad’s articlehttp://www.sql-server-performance.com/sql_server_log_shipping.asp in this regard. If you are using without a domain account then ensure to use local Administrator account or similar local account (name) between the servers having same password, otherwise the copy will fail. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

thanks guys. in fact i will be using a windows Task schedule to copy the backup file from one server to the other. Only this task will be using a domain account. Thanks for all the advice. I really appreciate this FORUM as everyone is always trying to help each other. Thank you for creating such a nice and useful community.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] AKTHAR

Glad to hear you are finding the site useful to resolve the issues, but on that aspect as a pre-cautionary measure I would suggest to go the way I suggested. Say if any changes happened to that account within the O/S then log shipping will have the failure which is nothing but a risk of losing the standby approach. I have had similar issues so suggested the better route. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

thanks for the advice sayta.
AKTHAR

Most of times I use custom log shipping instead of build in….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

i am having an issue when trying to restore the database. It seems that Sql is using the agent account to restore the database on the secondary server and it cant??? AKTHAR

sorted out
i have created a SP on the secondary server and create a liked server on the main server and then called the SP in the JOB Step. Thanks AKTHAR

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] That is the reason referred Brad’s article in this regard for such custom based log shipping. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

Thanks all for your help AKTHAR

Hi guys,
i have setup my jobs – to do a manual logship
BackupLog
CopyLog to secondary server
restore log. The steps runs successfully but i can find the updated information in my secondary database.
any help plz
AKTHAR

What do you mean not able to find update information on secondary database?
As it will be in LOADING state for next log restore, have you attempted to make it online looking for such records. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

i tried to do some changes in the main database, and then run the backup and restore log job several times. When i recover the secondary database, i dont see the change rows!!! AKTHAR

Have you used WITH RECOVERY option during that log restore on secondary database? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

i have used with norecovery? AKTHAR

Try to restore that log and see how many rows were affected.
Then how do you know that updated records on secondary database are not available. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

when i restore the tr logs manualle i can see all the datas! AKTHAR

?? Are you able to see the relevant data now? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Check the msdb..restorehistory table for your history…
If your logs are restored successful… then you should see the data…
You may be inserting the data into a diffent db or server…
or your log restores are failing… Instead of recovering the database you can restore as standby so that you can read the data without recovering the database.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

…also if you can see the log has been restored successfully on the error log of secondary server then it is applied, also you can keep a track of such actions by enabling log for that job. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

tried standby also, same problem[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] AKTHAR

If you are scheduling with a job then see whether job has executed successfully, also the duration to complete that job. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

yes all the jobs are successfully completed AKTHAR

How about the duration of each job?
Backup log -?
Copy log – ?
Restore log -?
How about service account between the servers? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi Akthar,
Did u check ur copy job was it successful??…..may be it wud have failed and accidentally u might not have viewed it…..check whether that particular tran log is available in both source and destination server…….. Regards
Deepak
SQL DBA

]]>