Automate backup using Windows Task Scheduler

Posted on

Question :

I want to run an automatic backup of SQL Server 2012 Express using Windows Task Scheduler.

I have 3 user databases. I want to backup all 3 user databases through a single stored procedure and a single batch file.

My environment is:

  1. Windows Server 2012 R2
  2. SQL Server 2012 Express
  3. Windows domain

I have gone through some related articles:

However, it is not clear to me how to make a stored procedure and run that stored procedure using a batch file?

Answer :

I recommend using Ola Hallengren’s backup solution.

The 6th question down on Ola’s FAQ page walks you through automating the stored procedures with batch files. The steps given in the FAQ can be used for any stored procedure not just his. Below is the example provided from the FAQ site. You can modify that to run your custom stored procedure.

sqlcmd -E -S .SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:Backup', @BackupType = 'FULL'" -b -o C:LogDatabaseBackup.txt

Leave a Reply

Your email address will not be published. Required fields are marked *