Restoring Server which utilizes Log Shipping

I have a large database that gets a full backup every night. We
utilize Log Shipping from this database to another database on a
reporting server. During the day Log Shipping creates log files on
the local server and truncates the transaction log for the database.
Everything works great. Lets say at 2:00pm the database crashes. I
restore the database from the previous nights full backup. Now I want
to restore the transaction log up to 2:00pm. The problem is that it's
empty because the log shipping has truncated it throughout the day.
What I do have is over 1000 small transaction files that were created
for log shipping. How can I apply all 1000 transaction files in the
shortest amount of time? I won't even think about doing each one
seperately.
WillThe Log shipping wizard will only truncate the log after it has backed it up
so they are still fine for a restore. Why do you have 1000 log files?
You only need the ones from the last full backup. How often are you issuing
log backups? You might consider doing differentials during the day as well
if you backup the log frequently to save restore time. Then you only have
to restore logs from the last Diff.
Andrew J. Kelly SQL MVP
"Will Wirtz" <WillCWirtz@.Yahoo.com> wrote in message
news:223f5e88.0407300924.10482dca@.posting.google.com...
> I have a large database that gets a full backup every night. We
> utilize Log Shipping from this database to another database on a
> reporting server. During the day Log Shipping creates log files on
> the local server and truncates the transaction log for the database.
> Everything works great. Lets say at 2:00pm the database crashes. I
> restore the database from the previous nights full backup. Now I want
> to restore the transaction log up to 2:00pm. The problem is that it's
> empty because the log shipping has truncated it throughout the day.
> What I do have is over 1000 small transaction files that were created
> for log shipping. How can I apply all 1000 transaction files in the
> shortest amount of time? I won't even think about doing each one
> seperately.
> Will|||We have over 1000 log files because we log ship every 2 minutes. We
need to do this for data accuracy on our reporting server. If we do our
full backup at 2:00am and then during the day say at 4:00pm our server
crashes. We can restore the database from the full nightly backup. But
then we need to roll the database forward to 4:00pm using the
transaction log. Since log shipping trunctates the logs after every log
shippment, this leaves us with a small transaction file that contains
the transaction for a 2 minute period.
As you can see we can build up alot of transaction log files quickly.
What we need to be able to do is run all fo the transaction files
sequentially until they are complete. Is there a tool to do this or any
process that someone has used?
Will
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Well you can use the TSQL commands such as RESTORE HEADERONLY to get
information about the logs. From there you can determine the proper order
to restore them but it will take a little bit of code to loop thru each of
the files and get the information etc. There are some 3rd party products
who do this type of thing as well. SQL LiteSpeed is one that I know of
that has a gui that will look in a folder and figure out the correct
sequence to restore the files with.
Andrew J. Kelly SQL MVP
"Will Wirtz" <willcwirtz@.yahoo.com> wrote in message
news:%23eVuCiReEHA.720@.TK2MSFTNGP11.phx.gbl...
> We have over 1000 log files because we log ship every 2 minutes. We
> need to do this for data accuracy on our reporting server. If we do our
> full backup at 2:00am and then during the day say at 4:00pm our server
> crashes. We can restore the database from the full nightly backup. But
> then we need to roll the database forward to 4:00pm using the
> transaction log. Since log shipping trunctates the logs after every log
> shippment, this leaves us with a small transaction file that contains
> the transaction for a 2 minute period.
> As you can see we can build up alot of transaction log files quickly.
> What we need to be able to do is run all fo the transaction files
> sequentially until they are complete. Is there a tool to do this or any
> process that someone has used?
> Will
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql