1

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?

  • 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 – Shanky Apr 10 '16 at 12:53
  • 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. – Christoph Wegener Apr 10 '16 at 13:03
  • 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. – Gary Apr 12 '16 at 13:27
  • @Gary, It's My Audit Production Server. Server migrate from EE 2012 to SQL Express 2012. – Md Haidar Ali Khan Apr 12 '16 at 13:37
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.