In our article, I would like to tell you about a simple but frequently asked subject.. As you know, automatic backups can be taken in SQL Server.. For example, a job can be defined so that backup is taken every evening at 20:00.. The service that operates this job is SQL Server Agent and there is no SQL Server Agent in Express, that is, free version of SQL Server.
As I mentioned in the introductory paragraph, Express editions do not have SQL Server Agent. Therefore, we will perform automatic backup with Windows’ own scheduler service.
The process we will do is actually simple.. We will prepare the script that will take backup and trigger this script with sqlcmd via windows scheduler service whenever we want.. By the way, windows scheduler task serves to run given tasks like SQL Server Agent at certain periods.
I will perform my example today on Windows Server 2008 Standard and SQL Server 2005 Express.. Although there are minor screen differences in other editions, the main logic is the same.
Now let’s move on to our steps.
We are preparing the backup script for the DB we want to backup.
BACKUP DATABASE [AdventureWorks]
TO DISK = N’C:AdventureWorksDailyBackup.bak’
WITH INIT
GO
This script i “E We save it as :SQLBackupsDailyBackup.sql”.
We will run this file on the Windows scheduler task with the following commands. The first command connects to the DB Server with windows authentication, the second command connects with a specific SQL Login.. You can use whichever is convenient for you.. I will use the first one.
sqlcmd –S serverradiinstancename –i “E:SQLBackupsDailyBackup.sql”
sqlcmd –S serverradiinstancename –U BackupAdmin –P password –i “E:SQLBackupsDailyBackup.sql”
By the way, edit the serverradiinstance name part according to your own server information. Instance name is usually SQLExpress in Express versions.. You can check what exactly happened in SQL Server Configuration Manager.
Now we open Task Scheduler to start the Task definitions.
So we press Task Scheduler Library >> Create Task to define a task.
General part according to our needs as follows
In the
Schedule section, we determine how often this task will run.. Our task will run at 11 every night.
In the
Action section, we define the action that will do the job by pressing New Action. This will run a program. In the Browse section, we select sqlcmd.exe. This exe is usually located in C:Program FilesMicrosoft SQL Server90Toolsinn. Finally, in the Add arguments section, we write the part of the command after sqlcmd, whichever of the commands I wrote before suits you.. For example, as follows;
sqlcmd –S serverradiinstancename –i “E:SQLBackupsDailyBackup.sql”
We close this screen by pressing OK.
Our Scheduler is now ready. We trigger it manually by right-clicking on the scheduler.
When the task is finished and finished, we will see that backups are taken to the relevant folder.
In our article, Express, which is the free SQL Server version, I focused on how to perform automatic backup with Windows Scheduler in Edition. In this version, which does not contain SQL Server Agent, you can use Windows Scheduler to handle this need without any problems, accompanied by the above steps.
Excerpt: Turgay Sahtiyan