SQL Script to Get DB Shell

Question

Post reply

SQL Script to Get DB Shell

dwh-1095413

Default port

Points: 1415
More actions
September 10, 2010 at 7:08 am

#226190

I know there is a feature in Management Studio that can generate scripts but this requires manual action. What I want to do is copy a database from 1 server to another but just the "shell" of the database. I don't want ANY data.
I need table structure, etc
Anyone have dynamic script that can do this? Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

Really, the easiest way is to right-click the database, select Taks, then click Generate SQL Scripts.

Make sure and go to the Set Scripting Options page and use Advanced to customize the output.

Pay special attention to: Script Logins, Script Object-Level Permissions, Types of data to script (default is what you want), and everything under Table/View Options.

That will do what you want. It would take a ton more time to do anything like this by hand.

dwh-1095413 Default port Points: 1415 More actions · Answer 2

This is what I want and I know how to do this but this procedure is a manual way. I need it automated. I am trying to schedule a task to copy the structure of a database on server A to a database on server B once a month.

Using the Generate SQL Scripts won't help because if a table is added to database on server A, it won't be copied to server B unless I manually generate a new script.

jerry-621596 (9/10/2010)
Really, the easiest way is to right-click the database, select Taks, then click Generate SQL Scripts.
Make sure and go to the Set Scripting Options page and use Advanced to customize the output.
Pay special attention to: Script Logins, Script Object-Level Permissions, Types of data to script (default is what you want), and everything under Table/View Options.
That will do what you want. It would take a ton more time to do anything like this by hand.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

Are you removing the tables on server b? If not, then the script needs to handle that.

SQL compare will do this (from my employer): http://www.red-gate.com/products/SQL_Compare/index.htm

You could also use Powershell to script this out. http://sqlblog.com/blogs/allen_white/archive/2008/01/25/using-powershell-and-sql-server-together.aspx