Monday 26 July 2010

Schedule SQL queries over night

   


Today, I would to talk about a incredibly useful application that I found while surfing. SQL Scheduler is "a fully functional client/server application written in C# that allows administrators to schedule various SQL jobs for SQL Server Express and other versions of SQL Server."(quoted from SQL Scheduler home page)
When I started a new intranet project back in 2008, I had to link different SQL Server databases, keep them backed-up and synchronised with other ACUCobol databases. I actually have to perform different operations over night, while people in the company are not querying the databases. SQL Scheduler can be programmed to do it while you're sleeping; it can send you email notification on the results and it is highly configurable, easy to use, and in the end it is a must for every SQL Server administrator. And - last but not least - SQL Scheduler is completely free.
With it I have solved different issues: first of all I can run backup routines every night, the application compresses the files and checks the databases. Then I run different jobs to keep the SQL Server and the ACUCobol dbs synchronised. As long as you know how to create SQL Server queries, you can instruct SQL Scheduler to run different jobs at different times, and only your imagination's the limit.

You can create a query for the backup of your db like this:
DECLARE @BackupName VARCHAR(255)
DECLARE @BackupFileName VARCHAR(255)

SET @BackupName = 'backupname_' + convert(nvarchar(20), getdate(), 112)
SET @BackupFileName = N'backuppath' + @BackupName + N'.bak'

BACKUP DATABASE intranet TO 
DISK = @BackupFileName WITH NOFORMAT, NOINIT, 
NAME = @BackupName, SKIP, REWIND, NOUNLOAD,  STATS = 10
I use a little .bat file to compress the file:
DECLARE @ArchiveName VARCHAR(1000)
Set @ArchiveName = 'backuppath\name.bat ' + @BackupName
exec master..xp_cmdshell @ArchiveName
The content of the bat file is just the command line parameters to compress the file.

As you can see, it is very easy to create a backup routine. The only thing you need after that, is to schedule the job. And you're set to go!

More information on SQL Scheduler

6 comments:

  1. Have you gotten this to work on Windows 7 or Server 2008? If so, how did you accomplish that? I have been trying to get it working on a Windows 7 machine with no luck.

    Thanks

    Bill

    ReplyDelete
  2. Bill,
    at the moment I am using it on a Windows 2003 Server. Is it crashing when run on Server 2008 and win7? SQL Scheduler should be fully compatible with Windows 7... Just as a first thought: have you installed .NET Framework 2.0 (be careful with the version)... Please, let me know if I can help you more.

    ReplyDelete
  3. I ran command prompt with admin rights first then executed the install.bat file from within cmd and the service installed correctly.

    ReplyDelete
  4. I'm glad it worked for you. I still use it every night and it works perfectly.
    (Probably Bill has found his way of solving the issue!)

    ReplyDelete
  5. Do SQLscheduler can run the jobs when you restart the server?

    ReplyDelete

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.