Schedule SQL queries over night

Tweet

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