Copy a SQL Server database with just the objects and no data

Question

Hello, I need to make a copy of a large SQL 2014 Prod DB and restore it onto another server Test instance but without the data. (It's to large to do regular DB backup and Truncate/Delete the data.)

I noticed this article explains using the "Extract Data-tier Application..." feature:

https://www.mssqltips.com/sqlservertip/4664/copy-a-sql-server-database-with-just-the-objects-and-no-data/

It mentions first: "To use a DAC package, we first need to download and install the Data-tier Application Framework, aka DacFx, from here."

Which server does this need to be installed? I'm aiming to do this from my workstation machine and do not want to install anything on either Source Prod or Target Test servers instances.

Also when doing the steps listed will/could this have any negative impacts on Source Prod and is it rather quick or any other things I should be aware of?

Thanks in advance.

Answer 1

There might be an easier way:

DBCC CLONEDATABASE(ProdDB, ProdDB_clone)

This command creates a clone of ProdDB, but without the data. Once you have the clone you can move it to the test environment with BACKUP/RETSORE.

Answer 2

Hi @techresearch7777777 ,

You can also using Generate Scripts wizard for scripts of all database objects. SSMS >Right click the database > Tasks > Generate Scripts. During the process, please choose script Schema only option as below screenshot.

Refer to the blog Methods to script SQL Server database objects to get detail steps.

---

If the response is helpful, please click "Accept Answer", thank you.

Answer 3

Thanks Erland.

Noticed it's a DBCC command...running this will it be intrusive and have any impact on the live Prod DB?

Just brainstorming what are the differences between Database Clone vs Extract Data-tier Application if they both make a copy of the source DB but without data?