asked on

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!

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlagent007

ASKER

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.

sqlagent007

ASKER

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....

chapmandew

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?

sqlagent007

ASKER

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.

chapmandew

I would be surprised if you'll be able to....

chapmandew

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.

sqlagent007

ASKER

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....