How to use SSIS to automate database restores..

Refreshing the development and test databases with production database backup is one of the routine work the DBA faces in his day to day environment. The developers will be testing in those development server and if some thing goes wrong again they will contact DBA's.

What If we find a solution to automate the database refresh using SSIS package. Yes!, We have a way to automate the process using SSIS. It is very easy to do this using a SQL Server Business Intelligence Development Studio.

Step 1:

   Analyzing the request carefully. We have to be clear with this information. The Source database, destination database are Important. (Be cautious if you do it vice versa it will impact the production)

Step 2:

Open the SQL Server Business Intelligence Development Studio.

SQL Server BIDS

Step 3:

It will open up the below window, We have to choose the "Create Project" to start a fresh project.

BIDS Opening page

That will lead you to next window. Here we have to choose what project we are going to do. We can create Reporting Services Project, Analysis Services, Integration Services etc., 

Now Choose "Integration services Project" then name your project and choose a location to store.

Step 4: 

Now we are ready to create our new SSIS project. In the left side of the Window you can find "Tool box". These are the predefined functionality's, which can be used for building complex SSIS packages. We will be using one of this functionality in this project. In the right side you can find our project which we named. It contains all the necessary components needed to run a package. It consists connection managers, Database names, XML and other components.

Step 5: 

    I am going to backup a database in one of the production server and restore it to the test server. It is better to use copy only backup for these kind of works. Copy only backup will not affect the backup plan which is being used for the production server.

So I will follow the below steps to accomplish this task:

1.  Backup the database to test server location
2. Change the test database from multi_user to single_user mode. (To disconnect the connections, if any)
3. Restore the database in the destination server.
4. Revert the databases from single_user to multi_user mode.

Let me explain you how to do this in SSIS.

Step 6:

Adding the source and destination SQL Servers in the Integration project. 

Below the work area, You could see a place for connection manager. Right click over that and click on "New OLE DB Connection"

It will open the window "Configure OLE DB Connection Manager"

Click on "New" to create a new connection manager. (You have to add all the SQL Servers which are all you are going to use) It will lead you to "Connection Manager" window. In the left side of the window there will be two options 1. Connection 2. All

In connection, specify the "Server Name" in the Name. You can even choose the default database by "Connect to Database" box. 

Click on All, here you will get multiple options. You can set the time out period, General time out etc., feel free to explore all options.

If you want to check the connection you can check using "Test connection" button. Click ok again ok to add the connection manager. Iterate the above process to add another connection. Don't forget to add all the SQL Server you are going to connect.

Step 7:

You are just a few steps behind to create a SSIS package. Now find "Execute SQL Task" from the Tool box provided in the left pane. Drag and drop it in the work area. Right click and click Edit.You will get "Execute SQL Task Editor" window.

Give the Name of the task. You can set time out. Default is '0'. You have to choose the SQL Query input method "SQL Source Type". There are three options 

1. Direct input

2. File Connection

3. Variable

As I am going to refresh a single database, Here I am using "Direct Input" method. Be cautious in choosing the "Connection". You can parse the query. (Here you might face a small issue!! Let me over come by yourself. Comment if you over come that issue!!)

Iterate the same process for restoring the database. I have used the following scripts

1. Backup Script

2. Restore Script

Connect the green arrow from one task to another task. It is called as the work flow. Now we have created a simple SSIS package which can be used for backup and restore. Simple, yet customizable and powerful. We can debug the package using the "Debug" option, which is represented by small green arrow symbol or by pressing F5. If there are errors it will identified during the debug process and rectified.

You shall save the package in File system as well as SQL Server. Saving in SQL Server is very helpful in adding this package as one of the step in the "SQL Agent Job"

Step 8:

Using this package in SQL Server job.

We have to save a copy of this job in the SQL Server. Go to File->Save a copy of <*.dtsx> as it will open the following window.

Choose Package location as "SQL Server". Provide the SQL Server instance, choose the package path and name the package, then click ok. The Package will be saved in the SQL Server instance.

Step 9:

Adding the package to the Agent job.

Connect to the SQL Server in which you want to run the package. Go to SQL Server agent, open a new job->Add a new step or add the step in the existing job.

Choose the type as "SQL Server Integration Services Package". Package source as "SQL Server". Provide the SQL Server name and choose the package. Click OK.

You are done!

Step 10:

You can use this job to restore the database when ever you need to refresh a test database. And of course you can use the scheduler to automate this. You may use maintenance plan execute only this package in a job. As I mentioned before you shall customize this for your environment. 

Let me know if this post helped you. Do comment your queries in the comment section, no moderation :).