?
Solved

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

Posted on 2009-02-24
8
Medium Priority
?
600 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:sqlagent007
  • 4
  • 4
8 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 23723127
0
 
LVL 1

Author Comment

by:sqlagent007
ID: 23723859
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
 
LVL 1

Author Comment

by:sqlagent007
ID: 23724059
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 60

Expert Comment

by:chapmandew
ID: 23724098
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
 
LVL 1

Author Comment

by:sqlagent007
ID: 23724208
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 23724225
I would be surprised if you'll be able to....
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 23724262
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
 
LVL 1

Author Comment

by:sqlagent007
ID: 23733989
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question