Automate backup using Windows Task Scheduler

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:

• How to schedule and automate backups of SQL Server databases in SQL Server Express
• Automated backups utilizing SQL Server and Windows Task Scheduler
• Schedule a daily backup with SQL Server Express

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

• Shanky: The links you posted are more than enough for what you need. And you specifically dont need any stored procedure if you do so put the query of Microsoft support article in procedure and then call the procedure in Task scheduler
• Christoph Wegener: I usually prefer to build SQL servers with Ed Vassie's SQL Server Finebuild scripts. The scripts have a step for configuring backup jobs as Windows Task Scheduler jobs when using Express Edition.
• Gary: If your described environment is for developing purposes, you could upgrade to SQL Developer for free (as of 31 March 2016). With Developer edition, you get SQL Agent.
• Md Haidar Ali Khan: @Gary, It's My Audit Production Server. Server migrate from EE 2012 to SQL Express 2012.

Answer 1

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:\Log\DatabaseBackup.txt