SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the BEST way to automate tasks on your server?


What is the BEST way to automate tasks on your server?

Author
Message
jpSQLDude
jpSQLDude
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 939
I have many servers I have to back up.

I am using Maintenance Plans to create files, but then I use batch files and Scheduled tasks to drive 7-Zip and Robocopy to do the work of compressing, encrypting and moving those files off the server.

But batch files are ridiculously limited -- there is no real error checking or handling. They work just fine when they work, in the best-case scenarios, but when things go wrong you might not even know about it, and then you don't have any backups. You most especially need backups to work in the worst-case scenarios!

So I was wondering: What tools other than batch files/command-line do you use to automate your admin tasks?

For example, how about VBscript? My concern with that is what allowed the ILOVEYOU virus, and may be a security risk and disabled on some production servers. I want my backup system to work on all servers.

How about PowerShell? I believe it is totally free, but it requires the .NET framework, and besides, at the enterprise where I work getting anything new approved to be installed onto a production server is a nightmare that takes months (at best).

And there are commercial tools like WinAutomation that seems to be exactly what would work for automating admin tasks, but again not only would I need to justify installing it, I'd have to make the case for buying it.

Once you create backups to files sitting on a hard drive, then what do you do??



Just FYI, here is a sampling of what I am currently doing for my backups:

I use Maintenance Plans to just do regular backups to files right on the same server.

Then I compress and encrypt those files with 7-zip (using a batch file and Scheduled Tasks). Then I copy all those small/encrypted files over my network to a central file server (using a batch file and Robocopy and Scheduled Tasks). I do hourly Log backups, so my loss-exposure -- even if the server blows up -- is 1 hour. (Some servers are even tighter.)

Then I also copy all those files across the network again to a big hard drive attached to my workstation (also with a batch file and Robocopy and Scheduled Tasks).

And finally I pull all those files to tape. If you don't have any backups, you can be -- and should be -- fired. No one ever gets fired for making too many backups!! :-D

The net result is I have a boatload of backups, copied all over the place, they are secure since they are encrypted, and I have a copy on my workstation so I can easily do restores and actually test the backups and validate they are working (and document the recovery process, etc, etc).

Its a pretty good system, when it works, which it usually does, but what happens if a server loses power during a really long Full backup?? A corrupt .BAK files is created, gets compressed, mirrored all over the place, and I think I have a backup but I don't!! Many similar ugly scenarios when relying on Batch Files..........
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31070 Visits: 10091
The best way to automate tasks on the server is whatever works the best for your situation. There is no one size fits all automation tool.

As for Powershell - if you are running SQL Server 2008 (you posted in the 2008 forum, so I am assuming that is what you are running), then Powershell is already installed. SQL Server 2008 comes with it's own version of Powershell that you can automate through SQL Server Agent - or Task scheduler.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

jpSQLDude
jpSQLDude
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 939
I wish I was running SQL 2008!

Actually -- believe it or not -- they have SQL 7, 2000, 2005 and 2008 running here... ugh! Yes, even NT 4 Server. Double-Ugh!

So I could use that newfangled PowerShell thingey on the modern servers, but -- the life of a DBA -- I have to support whatever they throw at me.

So I am having success developing a system using good old batch files, but as you can maybe see, it takes a LOT of time to develop and test Test TEST and make sure everything works, even in bad/rare situations -- so here I am geeking away on Saturday.

I was just hoping maybe someone had some magic insight that would save me a bazillion hours. But no, I shall script away on Saturday.

I need to get out of here and go talk to girls.

w00t
DrNo DBA
DrNo DBA
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 466
We use custom stored procedures and jobs for all our maintenance tasks, the backups hold a single copy locally and mirror/copy it to a remote dedicated backup server where we hold backups for about a week. This is cleared out by a simple vb.net program.

We are looking logging this in more detail and then reading it into a central server for reporting purposes so that we can get an idea of when and how the backups fails.

All routines are currently deplyed from a central server using a vb.net program, this is due to change when we upgrade the server to SQL Server 2008 R2 when we are going to use the deployment options in 2008.

Our estate is currently mainly 2005 with 2008 catching up fast with a few 2000 servers that we hope to upgrade by the end of the year.
AndrewSQLDBA
AndrewSQLDBA
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7890 Visits: 3427
I use SSIS for everything. There is just about nothing that I cannot do. Especially with SQL 2005. I can move files around, create directories. You can also do the same thing with DTS, just using SSIS is now easier.

And the best part, it is all free. Everything comes with SQL Server, of all versions. use one version, I would use SQL 2005, to do all the work. It has no idea what version of a SQL file it will be dealing with when moving files

Andrew SQLDBA
jpSQLDude
jpSQLDude
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 939
AndrewSQLDBA (2/22/2010)
I use SSIS for everything. There is just about nothing that I cannot do. Especially with SQL 2005. I can move files around, create directories. You can also do the same thing with DTS........

Andrew SQLDBA

Andrew -- thanks for the response. I so wish I was only using 2005 and later, would make life MUCH easier!

I am looking in 2000 at DTS, and I don't see any way to move files around or create directories or anything. Am I missing something? Can you point me in the right direction??

Thanx!
GTR
GTR
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2921 Visits: 368
jpSQLDude (2/20/2010)
I have many servers I have to back up.

I am using Maintenance Plans to create files, but then I use batch files and Scheduled tasks to drive 7-Zip and Robocopy to do the work of compressing, encrypting and moving those files off the server.

But batch files are ridiculously limited -- there is no real error checking or handling. They work just fine when they work, in the best-case scenarios, but when things go wrong you might not even know about it, and then you don't have any backups. You most especially need backups to work in the worst-case scenarios!

So I was wondering: What tools other than batch files/command-line do you use to automate your admin tasks?

For example, how about VBscript? My concern with that is what allowed the ILOVEYOU virus, and may be a security risk and disabled on some production servers. I want my backup system to work on all servers.

How about PowerShell? I believe it is totally free, but it requires the .NET framework, and besides, at the enterprise where I work getting anything new approved to be installed onto a production server is a nightmare that takes months (at best).

And there are commercial tools like WinAutomation that seems to be exactly what would work for automating admin tasks, but again not only would I need to justify installing it, I'd have to make the case for buying it.

Once you create backups to files sitting on a hard drive, then what do you do??



Just FYI, here is a sampling of what I am currently doing for my backups:

I use Maintenance Plans to just do regular backups to files right on the same server.

Then I compress and encrypt those files with 7-zip (using a batch file and Scheduled Tasks). Then I copy all those small/encrypted files over my network to a central file server (using a batch file and Robocopy and Scheduled Tasks). I do hourly Log backups, so my loss-exposure -- even if the server blows up -- is 1 hour. (Some servers are even tighter.)

Then I also copy all those files across the network again to a big hard drive attached to my workstation (also with a batch file and Robocopy and Scheduled Tasks).

And finally I pull all those files to tape. If you don't have any backups, you can be -- and should be -- fired. No one ever gets fired for making too many backups!! :-D

The net result is I have a boatload of backups, copied all over the place, they are secure since they are encrypted, and I have a copy on my workstation so I can easily do restores and actually test the backups and validate they are working (and document the recovery process, etc, etc).

Its a pretty good system, when it works, which it usually does, but what happens if a server loses power during a really long Full backup?? A corrupt .BAK files is created, gets compressed, mirrored all over the place, and I think I have a backup but I don't!! Many similar ugly scenarios when relying on Batch Files..........



I assume it is SQL 2008 is that correct ? What is the compression ratio of backup files with Robocopy? Once you answer these questions i can recommend you better way to achieve this.

EnjoY!
AndrewSQLDBA
AndrewSQLDBA
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7890 Visits: 3427
SQL DTS is very simple, and you use VBSCript as your coding language. All you have to do to create a function to move a file or delete a file, or create a directory, is to write it. So you are only limited to your own imagination, well, almost. using an OOP language will give you many more options. But still, with VBScript you can do many, many things. All of the things that you are talking, and more. And everything is in the DTS Pacakge, so there is no need in having to keep up with a bunch of little files stuck every where, for everyone to that has access to see, and even worse, modify or delete. Thinking that no one is using them. Even better, when you move the DTS package, the code goes with it.

Check out www.SQLDTS.com, there are plenty of examples. You cna modify them and you have something different for a different task.

I used DTS for many years before SSIS. I created and performed all kinds of tasks and things using DTS. I never used an old fashioned command file. Download the VBScript reference from microsoft site, so that you have all the syntax, functions, methods, etc.... that VBScript has

Andrew SQLDBA
jpSQLDude
jpSQLDude
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 939
GT-897544 (2/22/2010)

I assume it is SQL 2008 is that correct ? What is the compression ratio of backup files with Robocopy? Once you answer these questions i can recommend you better way to achieve this.

The newer versions -- 2005 and 2008 -- are easy. The hard ones are 2000 and 7.

That's why I'm trying to develop one system -- that works on any OS, any version of SQL.
jpSQLDude
jpSQLDude
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 939
AndrewSQLDBA (2/22/2010)
SQL DTS is very simple, and you use VBSCript as your coding language. All you have to do to create a function to move a file or delete a file, or create a directory, is to write it. So you are only limited to your own imagination, well, almost. using an OOP language will give you many more options. But still, with VBScript you can do many, many things. All of the things that you are talking, and more. And everything is in the DTS Pacakge, so there is no need in having to keep up with a bunch of little files stuck every where, for everyone to that has access to see, and even worse, modify or delete. Thinking that no one is using them. Even better, when you move the DTS package, the code goes with it.

Check out www.SQLDTS.com, there are plenty of examples. You cna modify them and you have something different for a different task.

I used DTS for many years before SSIS. I created and performed all kinds of tasks and things using DTS. I never used an old fashioned command file. Download the VBScript reference from microsoft site, so that you have all the syntax, functions, methods, etc.... that VBScript has

Andrew SQLDBA

Thanks Andrew -- I could actually code a mean ASP page back in the day, so getting back up to speed on VBScript should be pretty quick.

No concern about security? The ILOVEYOU virus came via .vbs -- no one ever turns that off on your production servers? Do you ever have to worry about STIGs or any other server-hardening guidelines??

http://en.wikipedia.org/wiki/Security_Technical_Implementation_Guide
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search