Automate SQL Server Express daily backup

I found a great article about automating SQL Server Express backups. I had to use this because we had an application using SQL Server Express 2012 and as you know you can’t create sql jobs to take backups.

The solution I used: scheduled tasks !

I altered one of the examples to my needs to create a daily backup, and another script to run once to clean up all old backups except the last one.
TIP: You can import one of the existing scheduled tasks and change them in the task scheduler.

I also created a new delete script, to delete all files older than 30 days

At first I used this line to see which files are found and will be deleted:
forfiles -p “C:\_backup\daily” -s -m *.* -d -30 -c “cmd /c echo @file

Next I just changed echo @file to del @path as shown in the example below:
forfiles -p “C:\_backup\daily” -s -m *.* -d -30 -c “cmd /c del @path

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

Sources:

 

Advertisements