Thursday, January 3, 2013

Automate SQL Express Backup

To Configure this properly follow the Information and below Steps:  

First Download Automate SQL Express Database Backup Script and files Click Here

You can use this to Automate SQL Express Database Backup on
SQL Server Express 2005,
SQL Server Express 2008,
SQL Server Express 2012,

See Video Tutorial
How to Automate SQL Server express backups on  YouTube :-

Step 1. Copy and paste the extracted file in c: drive including subfolder and files see the example below:


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

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

b. Click New Query

c. Copy all the text from [C:\Backups-All\Automate\SQL Script] [Automate SQL Express 
backups.rtf] and past it into New Query Window then click Execute. it should show [Command(s) completed successfully.]

d. Close SQL Server Management Studio.

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 backup and delete backup

a. Open the following folder C:\Backups-All\Automate\Schedul-Task-xml\

b. You will see 4 .xml files open the .xml file one by one into the text editor and replace the following TECH.PETERCRYS.COM with your computer name. see the bellow example:

<!-- Change below TECH.PETERCRYS.COM with your Computer Name  -->

<!-- Change below TECH.PETERCRYS.COM with your Computer Name  -->

c. Open the Task Scheduler goto -> run -> taskschd.msc

d. On the Task Scheduler click on Action -> Import Task -> [C:\Backups-All\Automate\Schedul-Task-xml] Import each .xml file one by one into the Task Scheduler. See the below example:

12AM-backup.xml : This will backup of the database in [C:\Backups-All\Backup\12AM] at 12:00 AM everyday 

DEL12AM.xml : This will delete database backup on 1, 11, 21, Last  at 11AM

12PM-backup.xml : This will backup of the database in [C:\Backups-All\Backup\12PM] at 12:00 PM everyday
DEL12PM.xml : This will delete database backup on 6, 16, 26  at 11PM
Note: You can edit the scheduled task anytime you want by double-clicking task -> target -> edit

See the Video Tutorial How to Automate SQL Server express backups :- 


In this video tutorial you are going to learn how to Automate SQL Server express backups using the sql server stored procedure, task scheduler and batch file.

 Video Tutorial By: Sachin Samy on YouTube:
IM: Skype: sachin.samy



  1. How to modify the scql script to overwrite the backup weekly or monthly???

    Email me at

    1. Hello there you do not have to SQl script to overwrite the backup weekly or monthly. it is done by a Scheduled Task from a batch file to take backup and delete the backup automatically. you can modify this from Scheduled Task. please let me know if you have any problem See Ya :)

  2. I have follow your instruction. Running SQL 2008R2 Express on Server 2008 R2 Standard OS. I run the task and the backups aren't created. It seems it is because I'm running it on a DC and the account/author ID used to run the task needs to be domain\administrator rather than hostname\administrator? I may be wrong because I have changed this and re-run the task but still not creating the backup files even though task scheduler reports Success.

    1. Hey there, yes from the user the task is running should have permission on sql server also check when your running the batch file manually does the backup is working. Let me know if you have any problem See Ya!

  3. Hi, it doesn't seem to create backups. In task scheduler at last run result, i get "The operation being requested was not performed because the user was not logged on to the network. The specified service does not exist. (0X800704DD)". I couldn't find a solution on this matter.

    1. Hi,
      Well you have to configure the task scheduler accordingly. In the task scheduler there is a option "Run whether user is logged on or not" you have to select this. Do let me know if you have problem see ya :)

  4. Samy,
    Backups not saving to folder. When i manually run them i don't see the dos screen popup and no files are saved. Task Scheduler says they ran fine. I am on as admin and can run the tasks no sure why command is not saving. I went to the Automate folder and tried running the bat files and same thing. I am on this server as a Domain Admin. Any ideas??

    1. Hey Seth,

      Check that your

      Default instance is: [ . ] [ dot ]

      sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"


      Computer name\Named Instance:
      [ .\SQLEXPRESS ] or [ computer name\SQLEXPRESS ]

      sqlcmd -S . -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"

      Do let me know if you have problem My Skype ID: Sachin.Samy see ya Bye :)

    2. hello there i have the same problem and your solution dont seem to work either

  5. Hii sir.
    I'm geting the error on open ms access db on visual studio 2008 says Could not find file C:\database Name.Mdb while its location is there.
    Please i need your help, thanks in advance.

  6. Hello sir,
    I am trying to get the backup files to a remote share on my network \\computername\folder\

    I believe it is a permission issue, and have tried several different users/computers in the scheduled task file.

    Any suggestions?

    1. Check out this link for backups of sql server on network
      let me know if you have any issue.

  7. Having a transparent image of the quantity of appliances you want to automatism can facilitate eliminate decisions that square measure dearer than your budget and a lot of sophisticated to fit your needs, for more information visit here Home Automation Fort Lauderdale.

  8. Hi, great piece of work thank you!
    This method backs up my two databases perfectly. Can you tell me - do I need to also backup the system databases in order to restore my databases as this script doesn't appear to back these up?

  9. i did your information step by step and this work when i manually run bctch-file.does this method completely work on windows 7 .

  10. Sir, Now i need to know whether it is possible to switch the backup location from C:// drive to any other drives like D:\\ , E:\\ or external storage HDD. If possible kindly help me. Thank you

  11. Considering SQL Express, work as user instance. When you have multiple user instances each one associated to a different user. In what master database should I create procedure. The main one or the one created at user profile folder?

    Also, is there any change on TSQL command?

  12. When you're a carpenter making a beautiful chest of drawers, you're not going to use a piece of plywood on the back, even though it faces the wall and nobody will ever see it. You'll know it's there, so you're going to use a beautiful piece of wood on the back.
    Funny Videos

  13. Hi Sachin, it worked successfully. Thanks.
    I would like to know if I can backup only the selected db. I knew about the exception but there's more db that is not necessary to backup and few needs regular backup. So, if I can only backup selected db !!!

  14. This comment has been removed by the author.

  15. Great work, but how to restore database from backup?

  16. I would like to thank you for your nicely written post, its informative and your writing style encouraged me to read it till end. Thanks google advanced search

  17. Can't wait to try following your guide. Adding video is a great idea!

  18. The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Projects for CSE.

    Software management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.

    Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training