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 :- http://youtu.be/hrE85Km7ync
 

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

[C:\Backups-All\]

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  -->
    <Author>TECH.PETERCRYS.COM\Administrator</Author>

<!-- Change below TECH.PETERCRYS.COM with your Computer Name  -->
      <UserId>TECH.PETERCRYS.COM\Administrator</UserId>

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: http://youtu.be/hrE85Km7ync
IM: Skype: sachin.samy

 

22 comments:

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

    Email me at ging_rojas8990@yahoo.com

    ReplyDelete
    Replies
    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 :)

      Delete
  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.

    ReplyDelete
    Replies
    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!

      Delete
  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.

    ReplyDelete
    Replies
    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 :)

      Delete
  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??

    ReplyDelete
    Replies
    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'"

      Or

      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 :)

      Delete
  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.

    ReplyDelete
  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?

    ReplyDelete
    Replies
    1. Check out this link for backups of sql server on network http://msdn.microsoft.com/en-IN/library/ms179313.aspx#NetworkShare
      let me know if you have any issue.

      Delete
  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.

    ReplyDelete
  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?
    Thanks
    Andy

    ReplyDelete
  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 .

    ReplyDelete
  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

    ReplyDelete
  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?

    ReplyDelete
  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

    ReplyDelete
  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 !!!

    ReplyDelete
  14. This comment has been removed by the author.

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

    ReplyDelete
  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

    ReplyDelete