CODEWITHSUNDEEP
sql-servert-sql
user386167
user386167

Reputation:

Clone a database schema without any data

I need our end-users to "clone" a database from our web application UI. I am able to "clone" a database by backing up a source database and restoring it into a new database. In this way, I "clone" the table schema and data.

My question is - is there any way I can "clone" just the table schema, without the data? I am aware that we can script the database manually, and run that script. But our table schema changes frequently (we add new columns and tables regularly) and we wouldn't want to update this script. Thank you.

Upvotes: 0

Views: 1445

Answers (2)

Max
Max

Reputation: 7090

Right Click over database -> Tasks -> Generate Script

On Set Scripting Options page click Advanced

General -> Types of data to script = Schema Only

With SQL Server data tools you can generate the schema difference and apply only the changes to target database.

Upvotes: 1

tschmit007
tschmit007

Reputation: 7800

basically you have to:

  • save the datatase
  • restore the backup to a new database (use the with move option)
  • connect to the new database and then exec sys.sp_MSforeachtable 'truncate table ?'

PS: you may have to disable FK constraints if any.

Antother solution: use Entity Framework Database First to build a small program. Launch said program with a connection string pointing to a new db.

Upvotes: 1

Related Questions