February 8, 2010 at 10:40 am
Hey all,
I was wondering if someone could maybe steer me in a direction here.
A client (SQL 08 Std) has their backups going out to the same drive as the datafiles.. now obviously, I slapped their wrists about this, they have 3 drives where the backups could go, the data file drive, the log file drive or the tempdb drive - right now its going to the data file drive because thats just how they have it set up. I considered dumping them onto the tempdb drive, not really sure if thats the best approach?
Also, they backup a folder on a different machine to tape - previously they have been manually compressing the files and moving them by hand across the network. I was looking for a better approach here, they can't use compressed backups as they're on standard edition (i believe they can't anyway) - so they have been using winrar to compress them. I looked at using a batch script but getting a bit bogged down getting the filename of the backup. I can pull the filenames I want compressed from msdb.dbo.backupset but I can't run winrar from a tsql window (lets assume they'll freak out if I want to xp_cmdshell - they probably wouldn't, but I really don't want to do that) - I could use sqlcmd.exe inside a batch script, but then I get lost even quicker.
Can someone give me a best approach on this? (assuming they are not willing to pay for any 3rd party software)
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
February 8, 2010 at 12:36 pm
Power shell is a good tool for you to do this. You can do what ever you want with Powershell. Check it out.
-Roy
February 8, 2010 at 12:59 pm
Roy Ernest (2/8/2010)
Power shell is a good tool for you to do this. You can do what ever you want with Powershell. Check it out.
Thanks Roy - appreciate the suggestion, but I'd need to learn ps from scratch, which i'd like to do, but just don't have the time :
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
February 8, 2010 at 1:04 pm
There are lots of sample scripts that does what you want..Search for it and you might get it. You will need to add/modify couple of things. If you know how to work with command shell, I think you will find this easy to do as well.
But you will need to test it thoroughly before you release it to production.
-Roy
March 6, 2010 at 10:31 pm
First...i don't work for Red Gate although I like their toys. So I would suggest for minmimal cost just a few hundred dollars they could pick up a SQL backup lite license from Red Gate. That will take care of the compression side for you. It can also write out to network paths (UNC) and has some nice network resilience features incase of network drops.
Even if you dont get the tool SQL can write the backups out directly to a unc path, just make sure the SQL agent account or proxy account has read/write permissions to the share. That will eliminate the need to move them by hand, and it will also off load the IO for writing out the backup to a different server or SAN. This can be accomplished via maintenance plans, manual T-SQL Scripts, etc. As for the Win RAR piece you could go the powershell route or you could configure the whole process including the backup using SSIS. The Execute Process task supports calling win rar and passing it arguments like the file name/path, etc.
Hope this helps,
-E
March 7, 2010 at 9:43 am
thanks willywonka, but as original post states, they weren't willing to pay for any 3rd party software.
i put powershell script together and got it done that way..
thanks anyway 🙂
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply