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:
- Windows Server 2012 R2
- SQL Server 2012 Express
- 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?
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