| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-07-20 : 19:49:01
|
I'm currently running SQL server standard edition for our website but for some reason I have found out that moving the logs to another server on a regular basis would probably be a good idea (after one year in production I found this out all by myself  ).I have one db-server and two webservers that are all completely stand-alone and using regular log shipping would not work because of the standard edition so I figured some sort of ftp to one of the webservers or something would do the trick...? Anybody got recomendations here??--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-20 : 19:54:33
|
| I believe Tara has some scripts for this on her blog. I've set it up using both ftp and robocopy in the past. Here is the general path I follow.Setup a logging table with status. Log the beginning of a log backup with filename (use a different filename for each log backup). Log when it's completed. On completion, copy the file to another server. You can get as fancy as you want from there, but that's the basic setup.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-07-20 : 20:06:53
|
| Umh, not sure I'm following here. I'm interested in the "on completion" part of it...could I use a procedure to to do the ftp for me? heres the deal:I have a daily (nightly) full database backup + a translog backup that runs every 3 hours. Both backups are to regular backup files on the filesystem of the database, and then a tape-backup is also done every night one hour after the full backup has completed. What I would like is for the translog backups to be copied to the webserver after creation but I would like to know in some detail how I would do that. And I might be on the slow side right now (it's 2am in Norway at the moment) but I don't see what taras procedures can do for me... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-20 : 20:11:07
|
| Ok. When your backups complete, write it to a table with the file name. Have a job that runs every 30 minutes and captures files in the table with copy_status = 0. Mark them as copy_status = 1 when they start copying, 2 for completed, 3 for failed for example. You can use ftp by using xp_cmdshell. You can also use DTS for this and use the ftp task.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-07-20 : 20:16:09
|
Hmm...something tells me I should not have used the maintenance-plan wizard to set up the backup for me  |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-20 : 20:17:25
|
| DOH :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-07-20 : 20:23:05
|
| Hehe, allright! You guys are pushing me hard here...but I'll take your advice and try to set up Taras procedures...I'll have to change my sig to "Ignorance is bliss" one of these days...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-07-20 : 21:54:13
|
| Tara: The procedures for full- and translog backup worked excellent but I took the liberty to modify them a bit as I only have one database and I therefore removed all the multiple db-backup stuff that was in here...I also added an output-paramter to return the filename of the newly created file. I will use this for moving it to another server. Hope you don't mind the mod's...if you're interested I'll send them to you to post in the blog. Umh...actually there was one thing I didn't get to work. I set the date on my laptop (on which I'm doing the tests) back by 3 days, ran a backup, ajusted the date back to the normal date and ran the backup again, but the "old" file wasn't deleted. Do I have to have a specific dateformat for the delete to work? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-07-20 : 22:00:54
|
| Ah, forget it...I messed up! It's 4am and I haven't gone to bed yet...and I don't drink coffee, procedures work like a charm :) |
|
|
|
|
Faking "Log shipping"
: