To take Automatic Backup SQL Express

Written by 5 Comments

How to take automatic SQL Express Server Backup?


Going to share experience about that how to take automatic backup of Microsoft SQL express by task Scheduled. As we know about Microsoft SQL express edition it's totally free that's why it has limited feature. In Microsoft SQL express edition have not feature to create job for automatic backup but still we have got to set automatic backup of SQL Express Server. Please follow below step by step to set automatic backup.



To download script and batch file for automatic SQL Express Server Backup Please click on Download.







Information and step to follow

Step 1. Copy and paste the extracted file in drive see the example below:

[c:\AutomaticSQLExpressBackup]

Step 2. Create a stored procedure in your master database:

a. Open SQL Express Server Management connect to database engine.

b. Click New Query

c. Copy all the text from [c:\AutomaticSQLExpressBackup\SQL Script] and paste it into New Query Window then click Execute ot Press F5. it should show [Command(s) completed successfully.]

d. Close SQL Esxpress Server.

Note: if you want to edit the stored procedure once it is created:

Expand Database > System Database > master > Programmability > Stored Procedure > Right click sp_BackupDatabases > Modify.



Step 3. Scheduled Task for a batch file to take Full and Differential backup:

a. Press Window+R then type Taskschd.msc.

b. Expand Task Scheduler library then create Basic Task

c. Put task name(SQLBackup) and description > Click on daily,weekly,monthly and so on according to you when do you want to task start.

d. Press Next > Click on start a program > Click on Browser and select your batch file > Finish

Note: You can edit the scheduled task according to you by double-clicking task > target > edit.


Hope this will help to all of you and resolved your all query if you have any query or question so you may ask to send email on our email account (onlinenetworkssolution@gmail.com) or leave comment on page.


Share:

5 comments

  1. Anonymous12 August 2017 at 11:04

    Kindly share what to do if i want to take backup of only one database & not all?

    ReplyDelete
    Replies
    1. Unknown14 August 2018 at 09:16

      do you find the answer?

      Delete
    2. Saiyyad Hussain27 August 2018 at 14:53

      Yes, You can take single database backup but in this case you have to create a batch file and map with task scheduler according to your requirement and then it will take automatic backup of single database.

      Please check below URL to take single database backup of sql.
      URL:- https://onlinenetworkssolution.blogspot.com/2017/01/to-take-backup-and-restore-sql-express.html


      If still you are facing same issue again so please let us know.

      Delete
  2. omar26 August 2017 at 15:31

    I always get the following error message after executing the query


    Msg 137, Level 15, State 2, Procedure sp_BackupDatabases, Line 30
    Must declare the scalar variable "@DatabaseName".

    ReplyDelete
  3. Unknown24 February 2020 at 22:22

    all ok but how to set backup local disk d folder arrange i am not now

    ReplyDelete

Please leave your comments...... Thanks