Do you need to refresh any database on a test environment or just test restores? See how Automated restore can help you!

In this post we are going to talk about the Automated restore option.
This feature restores the most recent full disk backup created with LiteSpeed and optionally differential and transaction log backups.
Where is the last backup located? What is the file name of the most recent backup? You don't need to ask this anymore as LiteSpeed will scan the folders/subfolders you specify for you.
As you can imagine this is very useful to refresh Development or Test environments and it can also help to test restores in non-production environments.
Having said that, let's start!
 
First of all: What is the problem we are going to fix in this example at our company?

PROBLEM:

The developers are complaining that the database they are using to code is a mess. It is refreshed from time to time manually but they say this is not enough. In 4-5 days the development and the production database are as different as night and day and this is the source of a lot of problems. They would like to start every Monday with a fresh copy. 

SOLUTION:

We will create a job that restores every Sunday the most recent database and any existing differential backups from the production database to the development environment.
And we will do this with the Automated restore option.

To create the Automated restore job, you can select this option directly in the Home Ribbon or Right Click the database you want to restore to in the Backup Manager:

 In our example we want to restore the AdventureWorks2012 database so we select it in the Restore Destination option.

 
In the Backup Source section, there are a few options:
I will select Folder scan as I want LiteSpeed to search the specified folder for the most recent database backups. 
I also want to restore the most recent full database backup and any existing differential backups based on this full so I select Full and differential option in Backup Type.
And, as I want the database to be restored, I select as restore type Restore database (as opposed to Restore Verify Only that I would select if I was just interested on knowing whether the restore works or not.)

QUESTION: Do we need to specify the SQL Server we want to restore from? Would it be possible that LiteSpeed populates this by itself?

ANSWER: No, you need to enter the SQL Server manually (LiteSpeed will show the local server by default if any). Note that it is possible that in one folder 
there are a lot of files from different servers so there is not a way LiteSpeed knows where the backups should be restored to.

Let's continue: What is the name of the last backup? Which folder did I save it to in the end? oh! I do not remember...I am not sure...what a mess...Anyway, I do not mind:  I will just add all the backups file locations I want to search and automated restore will look for them. I will also check "Search subfolders" and all the subfolders on the paths I add will be scanned. I have not been organized this time but nobody will realize!

Note: In this example we are restoring from disk but we could also select Cloud in the Restore from dropdown.

 

  In the Database Integrity section we will define the options to check database integrity after restore.

 In the Restore Options sections we find some interesting options. Even though they are self-explanatory, I will explain the first one:

Drop databases after restore: Use this option if you no longer need the restored database. For example, if you are only restoring the latest backup for testing purposes.  This option contains two additional options to select. One or both options can be selected.

        On success restore and check database integrity operations - The database is dropped after a successful restore and database integrity check.
        On failure any of restore or check databases integrity operations - The database is dropped after failing the restore or database integrity check.
 
I am not going to check this as I really want the database .
I will also check "Overwrite the existing database" as I just want one development database.

Let's see the Data Files section. 

I will leave these options by default.

Note: Although you can manually enter DATA and LOG locations, including secondary data files locations, it is recommended that you use locations generated by LiteSpeed.

Backup files can be restored immediately or restored in the background.

I want that every Monday morning the developers have a clean/refreshed database so I will schedule this to run every Sunday.

In the Notification section you can specify the notification of failure options that are sent after each restore. I select Do not use notification because this is a test for me and I am constantly receiving "Your mailbox is almost full" messages (Anyway this is another issue and LiteSpeed cannot help me with that :) ).

In any other case I would select Failure Only as I prefer to be notified if something does not work as expected.

 

 Now let's complete the wizard and we have finished!

 As always we can go to the Job Manager and check that a job has been created:

 


Hope that helps and thanks for reading!

Anonymous