TSQL create a job like the log shipping retore job to restore TLOG every 15 minutes

My goal is to move a database with as little downtime as possible. I would like to restore the database with "NORECOVERY" so it can take log restores every 15 minutes or so. I have created the new SQL server instance and I have my backup file there. I would like to restore the database to "stand by \ read only" and have a job restore logs that I will manually copy from production. So I will have a robocopy job run to push the jobs from my prod server to my new target server, then I would like an automated job that restores the newest logs every 20 minutes. When I have setup log shipping in the past, this type of job was created, however I am not allowed to partner my PRODUCTION instance with the new target server, so I am hoping there is a way I can do automated log restores on my new target server and database.

Thanks experts!
LVL 1
sqlagent007Asked:
Who is Participating?
 
chapmandewCommented:
0
 
sqlagent007Author Commented:
Well, here is the thing...I can't make ANY changes to PRODUCTION, this would include configuring a linked sever, ect. This company has very strict change control. I was hoping there was a way I could configure a job on the "stand by" server to just read through all the logs on the share and restore the newest one as it comes in...kinda like log shipping.
0
 
sqlagent007Author Commented:
so it looks like "sqllogship.exe" is what actually can read through the directory and restore from the last recovery point. Anybody ever done this manual?

I am guessing as long as the DB has a secondaryID it would work....
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chapmandewCommented:
all that log shipping is the restore of log files to a 2ndary server...I've done done this manually, but can't imagine it would be too hard to do.  why don't you just use what MS has built in?
0
 
sqlagent007Author Commented:
I thought what ms has built in is the "sqllogship.exe". Here is what is in the restore job command:

@command=N'"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqllogship.exe" -Restore B5280FDD-5289-4F36-948C-D9415A085DD7 -server TSTSRVR'"

I think in order for me to use the file that reads through the directory and restores from the last restore point, I need the "-Restore ID" as seen above...I am trying to figure out how I can do this with out running the log shipping wizard in PROD.
0
 
chapmandewCommented:
I would be surprised if you'll be able to....
0
 
chapmandewCommented:
the only thing I can think of is if you look in the msdb..log_shipping_plan_history table, but MS uses that for creating those guids when log shipping is setup through the wizard (or through TSQL)...you know what I mean?

why are you against using the wizard? It is going to be the easiest/safest way to do this.
0
 
sqlagent007Author Commented:
We ended up writing a custom TSQL script that restores every log in a directory. Sqllogship.exe" checks the last restore point and finds the next log in the series. Our script just restores every log in the directory, no checking....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.