Recently, we were working on a small project. The requirement was to create an empty copy of an existing database, meaning the new database could contain only database objects without data. The database was smaller and less complicated, so we completed the task using the Transfer SQL Server object task of SQL Server Integration Services. Later, we created a SQL Job to automate the process. You can read more about the Transfer database object task here.

In this article, I am sharing the step-by-step process. For demonstration, I have created a test setup with three virtual machines. The details are as follows:

Let us create an integration service project.

Create an integration services package

First, open SQL Server data tools / visual studio and create a new integration services project. Once the project is added, drag and drop Transfer SQL Server Objects Task to Control flow designer. Double-click on Transfer SQL Server Object Task to configure the task.

A dialog box opens. Here you can determine what objects you want to transfer/overwrite on the destination database. But before that, we must configure the source and destination connection and databases. To do that, Click on Source Database and select New Connection.

A dialog box opens. Here you can specify the source server’s hostname and credentials. If you use Windows authentication, enter a domain account; if you use SQL Server authentication, enter the user name and password. In this demo, the source database server is SQL01. I am using SQL Server authentication; hence I have added parameters accordingly. See the following image for reference:

Select Stackoverflow2010 as the source database from the SourceDatabase text box.

Similarly, configure the destination connection. In this demo, the source database server is SQL02. I am using SQL Server authentication; hence I have added a username and password accordingly. See the following image for reference:

Select Dev_Stackoverflow2010_Empty as the source database from the DestinationDatabase text box.

The next step is to select the object you want to transfer between the source and destination database. We are copying all objects from the source database to the destination database; hence I have configured the options as shown below:

Destination:

1. Drop object first: True.
2. Copy Data: False
3. Copy Schema: True
4. Include Dependent Objects: True

Destination Copy option:

1. Copy All Object: TRUE.

Table Options:

1. Copy Indexes: TRUE.
2. Copy triggers: TRUE.
3. Copy Primary Keys: TRUE.
4. Copy Foreign Keys: TRUE.

See the following image for reference:

Save the integration service package and project. As I stated, the Dev_Stackoverflow2010_Empty database is blank. During the first execution of the package, all objects will be copied from the Stackoverflow2010 database to the empty database. First, execute the following query to verify that the database is empty.

USE Dev_Stackoverflow2010_Empty
GO
SELECT 'User Table',object_id,name, schema_id,type_desc,create_date,modify_date FROM sys.tables
GO

Output

As you can see, the query did not return any records. Now, let us execute the package.

Once the execution completes, execute the same query to verify that the objects are copied successfully.

USE Dev_Stackoverflow2010_Empty
GO
SELECT object_id,name, schema_id,type_desc,create_date,modify_date FROM sys.tables

Output

For further testing, let us add a table and a stored procedure in the Stackoverflow2010 database by executing the following queries.

USE StackOverflow2010
GO
/*Create table*/CREATE TABLE ReputedUser(ID INT IDENTITY(1,1), displayname VARCHAR(100),reputation INT, aboutUser NVARCHAR(max))
GO
/*Create Procedure*/CREATE PROCEDURE sp_get_users
AS
Begin
SELECT TOP 100 u.DisplayName, u.DownVotes, u.Age, u.AboutMe, u.CreationDate, u.Reputation, u.LastAccessDate
FROM Users u
END
Go

Re-run the SSIS Package.

Once the execution completes, execute the below query to verify that the objects are copied successfully.

USE Dev_Stackoverflow2010_Empty
GO
SELECT 'User Table',object_id,name, schema_id,type_desc,create_date,modify_date FROM sys.tables
GO
SELECT 'Stored Procedure',object_id,name, schema_id,type_desc,create_date,modify_date FROM sys.procedures p
WHERE name='sp_get_users'
Go

Output

As you can see, the new stored procedure and table are added to the Dev_Stackoverflow2010_Empty database.

Summary

In this article, we learned the step-by-step process of creating an SSIS package with the Transfer SQL Server object task. There might be other methods that we can use to achieve the goal. The dbForge Studio for SQL Server has a copy database feature that helps make the process easier. All suggestions are welcome. I hope this article helps.