Logs, Logs, Logs.
-
Hello All,
Using SQL 2000.
I'm challenged with a task of copying log files from a share to a created directory. I first map a drive to the provided share and then copy the logs to a directory located on my server. My only problem is I can not delete the logs as I copy them from the share, thus, continually copying the same logs over and over again every hour that they are produced. I have created a scheduled task with the following code to run every hour thus copying new logs to be applied to a dedicated database. I then have a sql job running a stored procedure that takes the logs and applies them. How can I select only the next hours worth of logs everytime I run my scheduled task rather than all logs prior?
net use Z: \\Sharename$
cd Z:
copy Z:\Location\*_log.trn E:\Location
"can not delete"
del Z:\Location\*_log.trn I hope this is clear enough.
-
I would recommend log shipping or just doing a backup of the log then copying it...
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Not really what we're looking for. We are not doing the log shipping process. Would have worked, but decided against. Instead they are placing the logs in an available share for us to access. We then take the logs and copy them to a directory to be processed by a stored procedure in sql. Unfortunately, we are unable to delete the logs from the share as we copy them to our directory. So in order to automate this via scheduled task, we have to come up with a way of copying only the logs that are new to the share rather than all of them everytime. Any ideas?
-
Robocopy(Microsoft windows tool ) might help you
Mike
-
How about xcopy /d? Sounds like a possible winner!
-
How are the files named? If like most backups, they have a datetime stamp you can use that to weed out files.
eg: For a file name like myDb_tlog_yyyymmddhhnn.trn you can use xp_dirtree to list the files and save them in a temp table. Then convert datetime stamp into a datetime column and use it as filter criteria.
--------------------
Colt 45 - the original point and click interface
-
Hi,
You could write the filename to a table on your target system once the copy had completed (verify that the file is present in the target directory and that its details match those in the source using xp_fileexist or xp_getfiledetails) and then only copy files that were not in your table.
If the target system runs a regular purge on .TRN files then you can apply the same purge criteria to your table to stop it growing to large.
I use a control table for my home grown log shipping system (i have to make do with the low calorie standard version of SQL Server 2k
). The backup jobs write an entry into the table on the source server, and the target server pulls the next filename from the list and copies and restores it. If the copy or restore fails it will retry, and if the .TRN file is superseded by a .BAK file it will use that in preference.David
If it ain't broke, don't fix it...
-
Go for logshipping. Yo can save ur logs files with it.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply