Sql Server Database and Website Restore Solution.

I have created a really simple console program that will help restore your database and website. The idea came after I need to showcase a website demo for users to test but I want it to be reset every day, so I do not have to worry to re-populate some data as the data may have been changed or deleted by visitors to test the demo site.

This program is intended to use for Administrator only. It means you need to get an access to your server box which can be a dedicated server or VPS shared server. You will need to use Task Scheduler in Administrative Tools. The program is written in C# visual studio 2015. It uses the IIS dll file which is Microsoft.Web.Administration.dll to get access to IIS server to stop and start websites. So how does the program procedures work?

How to use this program?

In order to use this program, you will need to enter the correct setting information for the program. The setting configuration is written in xml format and will look like below.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <BackupDBDirectory>C:\Backup\</BackupDBDirectory>
  <RestoreDBDirectory>C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\</RestoreDBDirectory>
  <IISSites>
    <IISSite>site1.example.com</IISSite>
    <IISSite>site2.example.com</IISSite>
  </IISSites>
  <SourceDirectories>
    <Directory>C:\inetpub\wwwroot\site1.example.com</Directory>
    <Directory>C:\inetpub\wwwroot\site2.example.com</Directory>
  </SourceDirectories>
  <RestoreDirectories>
    <Directory>C:\backup\site1.example.com</Directory>
    <Directory>C:\backup\site2.example.com</Directory>
  </RestoreDirectories>
  <SQLServerDataSource>.\SQLExpress</SQLServerDataSource>
  <SQLServerUsername>sa</SQLServerUsername>
  <SQLServerPassword>1234567</SQLServerPassword>
  <SQLDatabaseNames>
    <Database>DBSample</Database>
    <Database>DBSample2</Database>
  </SQLDatabaseNames>
  <SQLDatabaseUsers>
    <DatabaseUser>dbSample</DatabaseUser>
    <DatabaseUser>dbSample</DatabaseUser>
  </SQLDatabaseUsers>
</configuration>

Here is the explanation of each setting item.

1. BackupDBDirectory
   

   This is the backup folder of the database you want to restore. You need to place all the .bak files into this folder.

2. RestoreDBDirectory
   

   This is the restore database folder. If you follow the standard installation of the SQL Server, the restored database should be located in the Program files. Alternatively if you do not know where it is located, you can easily open SQL Management studio and right click the properties of your database. You should be able to see the location of your .MDF data file of your database.

3. IISSite
   

   List of sites you want to restore the database.

4. SourceDirectories
   

   This is the site folder location. You can see the location of your website root folder in IIS.

5. RestoreDirectories
   

   This is the restore folder location. When the program is run, it will delete all folders specified in SourceDirectories setting and will be replaced by this restore folder.

6. SQLServerDataSource
   

   This will be the Sql Data Source Name.

7. SQLServerUsername
   

   This will be the sql account username to access to the Sql Data source. I used sa account in here to as it need to get access to Master database to perform the database restore.

8. SQLServerPassword
   

   This will be the sql user password to access to the Sql Data source.

9. SQLDatabaseNames
   

   A list of databases you want to restore.

10. SQLDatabaseUsers
    

    A list of database owner users related to each database you specified in SQLDatabaseNames setting.

How the program works?

This program is intended to run schedulely in particular time or day. You can set it up to run daily if needed in Task Scheduler. The way how it works is when the program runs, it read the configuration specified in setting.config file. It will then perform the following steps.

1. It will stop all the websites in IIS, specified in the IISSite setting.
2. It will restore the databases.
3. It will delete the site folders and restore the site backup files.
4. It will start the websites in IIS.

Basically that's how the restore program works. If you have any question, feel free to post your comment below.

Download Files

Download the program files

Download

Download the source files

Download

Download from Github