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?

Improve this question
4
  • 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
    Commented Apr 10, 2016 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
    Commented Apr 10, 2016 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
    Commented Apr 12, 2016 at 13:27
  • @Gary, It's My Audit Production Server. Server migrate from EE 2012 to SQL Express 2012.
    – Md Haidar Ali Khan
    Commented Apr 12, 2016 at 13:37

1 Answer 1

Reset to default
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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