Copying Structure of SQL Server Database Objects Without Data

Problem

“I am facing some problems while copying my database from one server to another without any data. For an instance, I need an empty SQL database with just the objects and no data, How can I perform it? Can any person help me to get out of the situation? Is there any simple way to copy an existing database structure and no longer considered the data? However, when I am trying to copy a SQL server database with just the object, it is showing me an error message.”

In the following section, we are going to describe the relevant solutions to overcome this situation.

Solutions

There are various solutions to this issue, users can do any of the following methods as per their need:

• The script comes out the source database and then, run the command on an empty target database file for creating all the database objects that are available in the source data.
• Consists the backup of the SQL Server database, restore it to the final destination database and then, delete each table that contains the data.
• Backup a source database, repair it within an intermediate database, also truncate all tables inside the intermediate database, and keep the backup of this intermediate database, and then, restore it to the new destination database.

Above steps work well, but one more solution, which is not known to all DBAs. Therefore, to utilize a DAC package file which is applicable for MS SQL Server 2008 R2 and upcoming versions.

For using a DAC package, first users must have to download the Data-tier Application Framework.

Backup a Microsoft SQL Server Database Without any Data

In general, MS SQL Server users mostly prefer to create the database backup for dealing with adverse situations. The server maintains the backup file that can be used to recover complete database.

Here, to follow the detailed steps by using SQL Server Management Studio (SSMS).

1. Under the SSMS Window, right-click on the AdventureWorks2012 database and select the Tasks. Then, choose the Extract Data-tier Application
2. Now, Extract Data-tier Application Window will begin. Now, in the Set properties screen enter the DAC package file location or even users can leave it. After that, click on the Next button
3. For further proceeding, click on Next until users can view the dacpac file successfully created
4. Finally, choose the Next option to close the Wizard. Now, an AdvantureWorks2012.dacpac file has generated.
   The DAC package file is ready after this, we can easily copy it to anywhere or place it inside a shared folder and then, we can also restore it to the final destination database.

With DAC Package, Restoring SQL Server Database

The DAC package must be restored to an instance of target SQL Server whose version either equal or higher than the source SQL Server instance as well.

Note: Even a single user can successfully restore a SQL Server 2014 DAC package to Server 2012 instance without any kind of issue.

The following steps to restore the data:

1. In SSMS Object Explorer Wizard, now connect to the target SQL instance and right-click on the Databases. Then, select the Deploy Data-tier Application
2. Now, the Deploy Data-tier Application wizard begins, choose the Select Package option and click the Browse button for searching the DAC package file location
3. Next, in the Update Configuration Window, type the specific destination database name or can leave it as the source database name
4. Click on the Next until the screen starts deploying to the destination database

Conclusion

In the above discussion, there are certain solutions discussed to copy database from the SQL Server without data. However, backing up a database with just the objects and without data cannot be complex and can be implemented in multiple ways that we have mentioned above. An automation perspective, the DAC package file is the best way, especially for multiple databases or one can also try SQL Server Database Migration Tool to move only structure of all database objects quickly.