Recreating existing Database Schema

Question

Recreating existing Database Schema

sgrimard

SSC-Addicted

Points: 466
More actions
March 3, 2016 at 7:33 am

#324659

Good day,
I am wondering what would be the easiest way to recreate an existing database schema (sprocs, tables, constraints, etc..) without the data.
In MSSQL, I know you can use the Generate Scripts in Task but this doesn't create a runnable script, you still need to order the execution manually since some tables might reference to other tables that aren't created yet.
What's the best practice? (Knowing that this is a large database...)
Regards,
Shany Grimard

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

Best practice, be efficient. I know it sounds bad, but use a tool. This is a problem that doesn't lend itself to using your own time and effort to build as there is complexity here.

The Generate scripts, if you build one script, should be runnable. Generate all objects and they should be ordered properly. If there a bugs in this process, I'd submit them to connect.microsoft.comhttp://connect.microsoft.com/.

If you want more options, filtering, etc., a tool like SQL Comparehttps://www.red-gate.com/products/sql-development/sql-compare/ does this very well and is worth the cost in time savings.

Disclosure: I work for Redgate.

Phil Parkin SSC Guru Points: 246961 More actions · Answer 2

You could try extracting a Dacpac and then deploying that.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jon.Morisi SSChampion Points: 12846 More actions · Answer 3

Jon.Morisi

SSChampion

Points: 12846

March 3, 2016 at 10:31 am

#1862841

Red-Gate +1

Lowell SSC Guru Points: 323518 More actions · Answer 4

sgrimard (3/3/2016)
Good day,
I am wondering what would be the easiest way to recreate an existing database schema (sprocs, tables, constraints, etc..) without the data.
In MSSQL, I know you can use the Generate Scripts in Task but this doesn't create a runnable script, you still need to order the execution manually since some tables might reference to other tables that aren't created yet.
What's the best practice? (Knowing that this is a large database...)
Regards,
Shany Grimard

if we are talking schema only/no data, the easier method is to generate the tables without the foreign key constraints, and generate the foreign keys separately. you can append them to the same file, but having all constraints added at the end solves the hierarchy order.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

If all you want is the structure, here's another vote for using the DACPAC. However, I'm with Steve (on multiple levels, I work with him), that if you need to do this regularly, the best way is with Redgate SQL Compare. It's all about the ability to automate and control what gets moved, when and how. If you're doing it a lot, we have the right tools for the job.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning