How to Automate backup of Web Server every night including all SQL Databases and also the DNS server if required using Batch file and Task Scheduler.
Intended audience: Web Server administrators, IIS administrators.
If you're working in small to medium size company you have to keep the IT budget in control and data protection is essential. There are so many ways to backup the web server some are cheap but not secure and some are secure but cost a lot of money. So i have created my own way to automate the backup of web server.
This will be easy to configure, secure and the best part is its totally free.
Important Note: Before you implement this method in the production server it is recommended that you test this method on your test environment.
Must Watch The Comprehensive Video Tutorial
How it works:-
There are two different sets of batch script's one is for Web Server and one for Local Machine.
Web Server batch script's:
We create a empty website in IIS which only works on custom port and defined the rule in IP address and Domain restrictions so the website will only be accessible from the specific IP address. It also adds the custom port in the windows firewall and creates a filter so that it will only accept the incoming request from the specific ip address this create a second level of security. There are 6 different batch files which work together to create a comprehensive, safe and secure backup solution. By scheduling the task in windows task scheduler we run all the batch files at a specific time in a sequence. First it takes backup of website and configuration, Second it will backup the SQL databases, Third converts the backed up data into a RAR archive, Forth starts the backup site and enables the custom port in firewall because when the backup site was created it was in stopped stage and custom port was disabled in firewall, Fifth once the predetermined time is over, 'so that we can download the backed up RAR archive, this will stop the backup website and disable the windows firewall custom port, Sixth deletes the RAR archive from the backup location.
Batch Files:
- 1-IIS-Backup.bat: This will backup all the website and configuration files
- 2-SQL-Backup.bat: This will backup all the Microsoft SQL Server databases.
- 3-convert-in-rar.bat: This will convert the backed up data into a RAR archive. The archive will be encrypted and password protected.
- 4-Enable-firewall-Port-backupSite.bat: This will start backup website and enable the firewall custom port.
- 5-Disable-firewall-Port-backupSite.bat: This will stop the backup website and disable the firewall custom port.
- 6-Remove-backup.bat: This will delete the RAR archive from the backup location.
Requirement:
Web Server OS: Windows server 2008, Windows server 2008 R2, Windows server 2012 .
WinRAR has to be installed on the Web Server.
Local Machine batch script's:
In local system we schedule two batch files in windows task scheduler. The first batch file will start the chrome browser. It targets the backup website url which we have configured on the web server. This starts the downloading of backup archive from the web server once the pre configured time is reached it closes the chrome browser and copies the downloaded archive to the different location inside the folder with the timestamp. Second batch file will deletes the backup from the downloads folder.
Batch Files:
- 1-Backup-all.bat: This will open the chrome browser targets the backup website url and downloads the backup archive from the server.
- 2-Remove-backup.bat: This will deletes the backup file from the download location.
Requirement:
Local System OS: Windows 7, Windows 8, Windows XP (manual configuration required for XP), Windows server 2008, Windows server 2008 R2, Windows server 2012.
Chrome browser has to be installed on local machine.
Follow the steps given below to configure :-
Step to follow on Web Server:-
Step 1. Copy and paste the extracted folder in c: drive including sub folder and files see the example below in Image 1:
[C:\Auto-backup\]
Image 1
Step 2. Go to the path shown below in Image 2:
[C:\Auto-backup\Automate\Batch-File]
Image 2
Step 3. Here you will find batch file Create-Website.bat right click and edit in notepad as shown in the Image 3:.
This batch file will create one new site by default the name of the site will be [Your-Website-Name], the physical path will be [C:\Auto-backup\Download_Backup], it will remove all the default Document entries from this site, In this site IP address and Domain restrictions it will also add allow entry rule for specific IP address and enable deny rule for unspecific ip addresses for this site, In windows firewall it will add a allow entry rule for specific ip address and port number all other IP addresses will be denied access for this port number.
The site is created in stopped stage and the firewall port will be disable.
In this batch file you have to change the below configuration as your requirement:
Change [Your-Website-Name] with the name of your choice.
This is physical Path of the site [ C:\Auto-backup\Download_backup ] don't change this.
Change this IP [192.168.1.1] with your Public IP Address.
Change this Port number [6185] with some other port which is not in use.
Image 3
After configuring the batch file saved it and then double click on the batch file to run see the Image 4:
Image 4
Check and Verify that there are no errors in the output window of the batch file.
Step 4. Right click and edit batch file 1-IIS-Backup.bat in notepad as shown in the Image 5:.
This batch file will backup all the Website and IIS Configuration to a predefined destination folder. You have to define the source path of the main folder where all the website are located. Don't change the destination folder path.
Image 5
After configuring the source path save the batch file.
Step 5. If you're using the Microsoft SQL Server and want to take the backup of all the databases or selected databases you have to use this batch file 2-SQL-Backup.bat
Note: You can skip step 5 and 6 if you don't want to take take the backup of SQL Server databases.
= Create a stored procedure in your master database:
a. Go to Run type ssms click ok see Image 6
Image 6
In SQL Server Management Studio connect to database engine see image 7
image 7
b. Click New Query see image 8
image 8
c. Copy all the text from [ C:\Auto-backup\Automate\SQL Script ] [Automate SQL Express backups.rtf] and paste it into New Query Window then click Execute. it should show [ Command(s) completed successfully ]. see image 9
image 9
d. Close SQL Server Management Studio.
Note: if you want to edit this stored procedure once it is created:
Expand Database -> System Database -> master -> Programmability -> Stored Procedure -> Right click sp_BackupDatabases -> Modify.
Step 6. Go to the path given below and edit the 2-SQL-Backup.bat see Image 10
[C:\Auto-backup\Automate\Batch-File]
Image 10
This batch file executes the stored procedure sp_BackupDatabases which you have created in SQL server on step 5. This will backup all the databases except those which are excluded in the Stored procedure.
In this file you will find Transact-SQL statements.
Remove Colon [ :: ] from the starting of the Transact-SQL statements which you want to use
See Image 11
Image 11
After you have configured save the batch file.
Step 7. Edit the 3-convert-in-rar.bat see Image 12
This batch file is using WinRAR command line parameters. When this batch file runs all the data which was backed up in this folder [ C:\Auto-backup\Download_backup ] will be added to RAR archive. The archive will be encrypted and password protected.
Here you have to change where it says [YourPassword] with the password of your choice for the RAR archive. if you want to extract the RAR archive you have to use the same password which you have entered here.
Image 12
After you have configured save the batch file.
Step 8. Edit the 4-Enable-firewall-Port-backupSite.bat see Image 13
This batch file will start backup website and enable the firewall port.
Here you have to change the backup website name and the port number with the same name and port which you have configured in Step 3.
Image 13
After you have configured save the batch file.
Step 9. Edit the 5-Disable-firewall-Port-backupSite.bat see Image 14
This batch file will stop the backup website and disable the firewall port.
Here you have to change the backup website name and the port number with the same name and port which you have configured in Step 3.
Image 14
After you have configured save the batch file.
Step 10. Edit the 6-Remove-backup.bat See Image 15
This batch file will delete the RAR archive from the backup location [ C:\Auto-backup\Download_backup ] See Image 15
Image 15
Step 11. Schedule task in windows task scheduler to run the batch files at the specific time.
a. Open the following folder [ C:\Auto-backup\Automate\Schedule-Task-xml ] here will find 2 xml files [ Start-Web-Server-Backup-1AM.xml ] and [ Stop-Backup-Website-2AM.xml] open the xml file one by one into the text editor and replace TECH.PETERCRYS.COM with your computer name. see the below example and Image 16
<!-- 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>
Image 16
After replacing it with your computer name save both of the xml files.
b. Open the Task Scheduler goto -> run -> taskschd.msc See Image 17
Image 17
c. On the Task Scheduler click on Action -> Import Task -> [ C:\Auto-backup\Automate\Schedule-Task-xml ] Import each xml file one by one into the Task Scheduler. See the below example Image 18, Image 19 and Image 20
Image 18
Image 19
Image 20
[ Start-Web-Server-Backup-1AM ] : This will take the backup of the Websites, IIS configuration files and SQL databases. After that it will converts the backed up files and folders into one single RAR archive. The archive will be encrypted and password protected. Then it will start the backup site and enable the custom port in firewall. This task will run 4 batch files 1-IIS-Backup.bat, 2-SQL-Backup.bat, 3-convert-in-rar.bat and 4-Enable-firewall-Port-backupSite.bat. It's scheduled to run everyday at 01:00 AM.
[ Stop-Backup-Website-2AM ] : This will stop the backup website, disable the custom firewall port and deletes the backed up RAR archive. This task will run 2 batch files 5-Disable-firewall-Port-backupSite.bat and 6-Remove-backup.bat. It's scheduled to run everyday at 02:30 AM.
Note: To edit the scheduled task right click on the task and goto properties.
The Web Server side configuration is completed. Now we are going to configure our Local Machine.
Step 1. Copy and paste the extracted folder in c: drive including sub folder and files see the example below in Image 1:
Image 1
Step 2. Go to the path shown below in Image 2:
[C:\Downloaded-Backup\Batch-File]
Image 2
Step 3. Open batch file 1-Backup-all.bat in notepad as shown in the Image 3.
This batch file will start the chrome browser and connects to the backup website url which downloads the backup from the web server at the specific time which you have configured. After the backup downloading time is completed it will close the chrome browser. Then it copies the downloaded backup to the different location.
In this batch file you have to change the below configuration as your requirement:
- Change this IP Address [ 192.168.1.100 ] with your Server IP Address.
- Change this Port number [ 6185 ] with the custom port your using on the server for backup website.
- Change this [ YourArchive ] name with the name of the Archive you have changed on server.
- Change this [ TIMEOUT 2700 ] as your requirement. you have to check how much time it takes to download the backup from your server. You also have to delay the scheduled task [ Stop-Backup-Website-2AM ] which is configured on server.
- Change this drive letter [ e: ] with your backup drive location.
Image 3
After you have configured save the batch file.
Step 4. Open batch file 2-Remove-backup.bat in notepad as shown in the Image 4.
This batch file will deletes the backup from the location where it was downloaded.
If you have changed the path of the download location in chrome browser then you also have to change this path.
Image 4
After you have configured save the batch file.
Step 5. Schedule task in windows task scheduler to run the batch files at the specific time. just like you have configured on the server.
Open the following folder [ C:\Downloaded-Backup\Schedule-Task-xml ] here will find 2 xml files [ 1-Backup-all.xml ] and [ 2-Remove-backup.xml ] open the xml file one by one into the text editor and replace TECH.PETERCRYS.COM with your computer name and username with your login name. see the below example and Image 5
<!-- Change below TECH.PETERCRYS.COM with your Computer Name and the username with your login name -->
<Author>TECH.PETERCRYS.COM\username</Author>
<!-- Change below TECH.PETERCRYS.COM with your Computer Name and the username with your login name -->
<UserId>TECH.PETERCRYS.COM\username</UserId>
Image 5
After replacing it with your computer name and the username save both of the xml files.
Step 6. Open the Task Scheduler goto -> run -> taskschd.msc See Image 6
Image 6
Step 7. On the Task Scheduler click on Action -> Import Task -> [ C:\Downloaded-Backup\Schedule-Task-xml ] Import each xml file one by one into the Task Scheduler. See the below example Image 7, Image 8 and Image 9
Image 7
Image 8
Image 9
[ 1-Backup-all.xml ] This will start the chrome browser targets the backup website url and downloads the backup archive from the server and copies the downloaded archive to the different location inside the folder with the time-stamp. It's scheduled to run everyday at 01:20 AM.
[ 2-Remove-backup.xml ] This batch file deletes the backup file from the download location. It's scheduled to run everyday at 02:30 AM.
Note: To edit the scheduled task right click on the task and goto properties.
Finished
Don't forget the comprehensive Video Tutorial:
How to Automate Web Server backup including all SQL Databases YouTube :-
If you're having any technical problem or have any suggestion post your comment here or catch me on:
- Google+: petercrys1
- Gtalk: petercrys1
- Facebook: Sachin.Samy1
- Twitter : @petercrys
- Skype: Sachin.Samy
